There are several ways to create a Calendar Table [Date Table] in Power BI using mostly DAX, but in this article, I want to share an M Function Script with you to create your Calendar Table or Filtered Date Table with 3 steps in Power Query.
I believe that there is no significant difference between calendar tables created by DAX or Power Query. You can select one method (DAX or PQ) based on your requirements and report scenario. For Instance, if you want to add Holidays to your calendar table, you need to use Power Query.
On the other hand, sometimes you need to have a table with a list of specific dates like the first day of the month, the Last day of the month or a particular day of each month like day 14, which is not that easy to be created by DAX.
Creating a Calendar table or a data table in Power Query is much easier than DAX; you just need to follow these 3 steps to create your desired table without writing or using any codes.
Step 1: Copy and Past Script to the blank Query
Open Power BI Desktop, click on the Get Data and select Blank Query
Open Advance Editor
Remove all preset content in the Advance Editor
Download and Copy the script
Paste the script in Advance Editor and press DONE
Download the Script:
Step 2: Fill out fields
fxDate has 3 fields: Day, Start.Date and End.Date, these fields need to be configured based on the required table:
Day: You can enter one of the below items in this field to create your table:
All: If you want to have a calendar table with all dates between the selected start date to the end date, enter "all" in this field.
Last: If you want to create a filtered data table with dates of the last day of each month from the month of the start date to the end date, enter "Last" in this field.
Number: You can enter a number from 1 to 31 in this field to have a filtered data table with the list of the selected day for each month from the month of the start date to the end date.
Start.Date: The first date on the Calendar table. your calendar table will start from this date, or if you select a particular day of the month in the Day field, your table will start from the month of the date chosen in this field [Start.Date].
End.Date: This field is an Optional field and has 2 options:
Select a date: the date chosen in this field will be the maximum date in your calendar table
Leave it blank: if you leave this field blank, you will have a Dynamic Calendar Table that will use the current date as the End date. (It will be updated when you update your report)
Step 3: Click on the Invoke button
When all fields are set, you just need to click on the Invoke button to have your Calendar Table.
The Output table (Calendar Table) will have these columns as follow:
Date | Month-Year | Year | Month Number |
Month Name | Year Month Number | Quarter | Year & Quarter |
Day of Week | Day Of Week Name | First Day of Month | Last Day of Month |
let's check some scenarios to see how to use this query:
IF you want to have a calendar table from 1st of January 2021 to the 30th of September, configure fields as below:
Day = All (To create a table with all dates between the start date to tde end date)
Start.Date = 01-Jan-2021
End.Date = 30-Sep-2021
IF you want to have a filtered date table with the last day of each month from Jan-21 to Dec-21, configure fields as follow:
Day = last [if you want last day day]
Start.Date = 01-Jan-2021 (Any date in Jan-21)
End.Date = 30-Dec-2021(Any date in Dec-21)
IF you want to have a filtered date table with the 1st or any day of each month from Jan-21 to Dec-21, configure fields as follow:
Day = 1 [if you want 1st day]
Start.Date = 01-Jan-2021 (Any date in Jan-21)
End.Date = 30-Dec-2021(Any date in Dec-21)
Day = 15 [if you want day 15 for each month]
Start.Date = 01-Jan-2019 (Any date in Jan-19)
End.Date = 10-Nov-2021(Any date in Nov-21)
IF you want to have a Dynamic Calendar Table or filtered date table to show all dates from the start date to the Current Date, leave the End.Date blank:
When you created your table by fxDate, you can change the fields in the table to have a new or updated table:
Summary
One of the most important tables in your data model is the Calendar table; if you want to use DAX time intelligence functions, you must have at least one calendar table in your model.
I hope this Power Query Function helps you to create your calendar table or filtred data table easily.
I am happy to read your feedback, so share your thoughts with me in the comments section below.
Comments