Power BI Online training in Australia

Power BI Online training in Australia

Creating a basic Calendar lookup Table

You are currently viewing Creating a basic Calendar lookup Table
In our last Power BI training in Sydney, we noticed that as a beginner, users normally create a calendar dimension table in Excel and connect and transform via Power Query. This approach is fine for an ad hoc analysis but is not a best practice while creating professional level dashboards. Calendar tables are an important part of data model because they allow the user to compare values over time. They are a powerful element of the data model and add time intelligence to the model. A calendar table typically contains a row for every date in the data set. Dates are not duplicated in a calendar or date table. The table can span over multiple years depending on the business requirements. The same date table can be used for different analysis such as analyzing sales against shipping date or the order date.  Date tables are also important to aggregate data for fiscal year calculations.

Best practice

As 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 Query

To 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.

Conclusion:
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

 

Tags: ,

Leave a Reply