Best practiceAs a best practice create a rolling calendar table in power query or using DAX. You need not to learn ‘M’ language to do this rather there is a standard code that you can use as a pattern in all your dashboards.
Rolling Calendar in Power QueryTo create a rolling calendar in Power Query: 1. Open Power BI desktop. 2. Click on Get data. 3. Click on Blank Query. 4. Rename it as Rolling Calendar (or any other name as you like)
5. In formula bar type the starting date (in yyyy, mm, dd format) of your calendar lookup table in a particular format
6. Press enter.
If your staring date is 1st Jan 2015, then your code should look like the following.
7. Click on the fx to add a custom step
8. Paste the following code.
9. Convert the resulting list into a table by clicking on ‘To table’ in ‘List tools tab’.
you will see the following pop up. Leave the default options as it is
10. click on
11. Rename the resulting column as Date
12. Change the format to Date.
You will have an automatically growing calendar in Power BI.
Calendar table using DAX
Date is an important dimension table in any data model. Date Table can be generated in Power BI using either the CALENDER() function or the CALENDERAUTO() function.
Both of these functions generate a single date column table. However, CALENDER(,) requires the starting and ending dates as the arguments to the function. Whereas, CALENDERAUTO() automatically detects the first and last date in the dataset and generates a date table accordingly.
Continue using the Previous file for this exercise
To Create a Date Table:
1.Click on Modeling Tab in the Ribbon.
2.Click on New Table.
3. In the Formula bar type the following DAX expression.
4. Click on New Column in the Modeling Tab and Type the following expression in the formula bar.
5. Click again on New column in the Modeling Tab and type following DAX in the Formula Bar for Month.
6.Finally, click on the Date table in the Fields Pan.
7. .Click Mark as Date Table.
8. Choose date from the Date column drop-down
9. Click OK.
You can also create a calendar lookup table using DAX. If you have Sales Data table and its relationship with Calendar Lookup is on OrderDate, your code should look like this.
Date is an important table in any data model as it allows the users to perform calculations and analysis over time. The date table is also necessary for time intelligence functions to work. Date tables can be created in Power BI using the M-language or the DAX language. This blog provides a step-by-step guide to create the calendar table. DAX is preferred over the M-language while creating the calendar table because it provides a lor of customization.
Are you a data analyst and want to learn more about Power BI? Why not sign up for Power BI training in Australia. We provide our services in the following regions
- Power BI Training in Sydney
- Power BI Training in Melbourne
- Power BI Training in Brisbane
- Power BI Training in Canberra