Power BI Intermediate Level: 49 - Plotting over Dates Using a Custom Calendar Table

Table of Contents?|?Power BI Report File?| Sample Input | Sample Input 2

Short summary: By using a custom calendar table, you can plot data from different related fact tables in the same visual. The custom calendar table allows for different date granularities, such as year, quarter, month, and calendar week for plotting and filtering.

Let's now put the custom calendar table which we created in the previous article to good use.

Setting up the Relationship

Go into the model view and drag and drop the Date column of the calendar table into the Date column of the transactions table. This creates the relationship where one date row from the calendar table can filter many transaction rows in the transactions table. If you position the dimension tables above the transaction tables, you can think of it as filtering flowing downwards like water, but not upwards. You can refer to my article on data modelling and table relationships for more information.

Plotting the Data

Let's create a line chart and plot the sum of sales over year, month, and calendar week by using the columns from our custom calendar. This works because we created the table relationship. Here we have the first advantage that we can use custom columns, such as the calendar week, which is not part of the automatic calendar functionality if you were to plot over the Date column of the fact table.

Adding a Second Fact Table

Let's import a second fact table. This is daily weather data from NASA for Cologne, where the stores are located. The raw CSV file requires some processing in Power Query, like removing the top rows which contain comments. The only tricky bit is creating a date column from the year, month, and day columns of the input data. For that we can add a custom column and use the #date function in Power Query to build a date from year, month, and day:

#date([YEAR], [MO], [DY])        

With the proper date column in place, can can create the table relationship between calendar table and temperature table.

You see in the model view that the calendar table can filter both the transactions table and the temperature table simultaneously. Let's try that out!

Plotting the Secondary Fact Table Data

Add the Average Daily Temperature for Cologne on the secondary y-axis and choose summarization average to get the average temperature for each calendar week.

Now you plotted date from two different tables over the same time axis. So that is a major payoff of using our custom calendar table. You can now even create measures to correlate data from different tables. And in principle you can add more fact and dimension tables, create the relationships, and then use them all together in the same visual.

Conclusion

Using a custom calendar table with table relationships allows you to analyze and visualize time-series data from different fact tables. You can use different granularities such as calendar weeks and the results are dynamically calculated and plotted. The user can also drill up or drill down the date hierarchy, for example to see just monthly data, without requiring any extra effort. All of this flexibility makes Power BI very powerful for time series processing.

In the next article, I will show you how to apply time intelligence functions to the calendar table to dynamically calculate measures such as month-over-month growth.

Please like, share, and subscribe and feel free to ask questions in the comments below.

Next article: Creating Month-over-Month and other Time-Based Measures