In Power BI there are two DAX functions that you can use to generate a date series that is CALENDAR and CALENDARAUTO.
The CALENDAR function requires two input parameters that is start date, and end date and returns a table containing a single column named date which generates a continuous series of dates from the specified start date to the specified end date.
SYNTAX
Calendar(<start date>, <end date>)
Similarly, The CALENDARAUTO function returns a table with single column which contains a contiguous set of dates.
SYNTAX
CALENDARAUTO([fiscal_year_end_month])
Although, these function are sufficient to produce a date series, but you can modify the function and get a complete calendar table by adding other date part also such Year, Month, Quarter, Day of week, MonthName, Day of week name, and YearMonth etc.
Lets create a complete calendar table for year 2020.
Following DAX uses a calendar function which takes two arguments that is start date and end date.
As we want to generate a calendar table for year 2020 so provided a start date as 1st Jan 2020 and end date as 31st Dec 2020.
CalendarTable_2020 = CALENDAR( DATE(2020,1,1), DATE(2020,12,31))
Once you commit the DAX, It creates a table named CalendarTable_2020 in Data model as shown below.
To see the output, you can quickly go to data view as shown below.
You can see, dates range starting from1st Jan 2020 till 31st Dec 2020.
Lets add a new columns to this Calendar table to display other parts such as Year, Month, Quarter, Day of week, MonthName, Day of week name, and YearMonth etc.
For this we will use a ADDCOLUMNS function that returns a table with new columns specified the DAX expression as shown below.
CalendarTable_2020 =
VAR CalTbl =
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) )
RETURN
ADDCOLUMNS (
CalTbl,
"Year", YEAR ( [Date] ),
"Quarter", QUARTER ( [Date] ),
"Month", MONTH ( [Date] ),
"Day", DAY ( [Date] ),
"Day Of Week", WEEKDAY ( [Date] ),
"YearMonth", YEAR ( [Date] ) & MONTH ( [Date] ),
"Day Of Week Name", FORMAT ( [Date], "DDDD" ),
"Month Name", FORMAT ( [Date], "MMMM" )
)
As you can see, a calendar table is generated. You can also adds other date parts also as per your requirement. Just explore the FORMAT function capabilities with Date for this.
Now you can quickly verify whether it generates all the dates for whole year or not. Just take the table’s data on visual and counts the number of day in each month.
for this just take clustered column chart on report page then drag a fields Month Name, Year on chart’s Axis and field Day to value.
You can see, there is a count for each month that seems fine, but you will notice that a month name is not coming in chronological order.
If you want to sort the month name in proper chronological order you can use a sort by columns feature of Power BI.
Sorting the Month Name in Chronological order
For this just click in Data View, then click Column Tool tab.
Then Select The Month Name column from result grid, then go to Sort by column icon and click on that and select Month.
That means values in Month Name columns you want to sort them based on values in Month column.
As soon you finish the step, you will see a order of Month Name on visual has been sorted in chronological order.
Also Read
Data Visualization with Power BI
