Home » Create a calendar table in Power BI

Create a calendar table in Power BI

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(2000,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

CALENDARAUTO

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.