Custom Time Intelligence Tricks in Power BI
Two Custom Time Intelligence Tricks to Improve the Usability of Your Report
Introduction
The time dimension is the only dimension which is essentially part of every data warehouse or dimensional data model. Without having this time relation most performance indicators would be meaningless. In this article, I will demonstrate two simple but effective ways to make time selections in Power BI reports more flexible and user-friendly. The first one is about setting a default selection using a normal slicer element as opposed to using the rather static relative date filter option. And the second one is about implementing a slider to move back and forth in time as you please or even a combination of both. This is especially useful for the purpose of rolling forecasts or business reviews as it allows an extended view of the expected performance. Both of these methods also work by using Microsoft’s Tabular models in Analysis Services databases.
Default Time Selection
Once you drag a date formatted field into the slicer visual, Power BI natively provides you various different time filtering options such as setting specific time limits, time ranges or applying a relative data filter to today’s date. While these approaches might provide a good starting point when opening a report, they are rather inflexible when it comes to user interactivity and deeper analysis.
One way to achieve a more interactive default selection is to add a “Calculated Column” to the time dimension. In this new column can then be checked and replaced the desired default selection with any plain text using a quite simple DAX formula. In this example, the current date value gets replaced with “Today”. Of course, this requires the new calculated column’s data type to be of “text” and the date value needs to be converted accordingly in line 4:
You can now select the element “Today” as the default selection when saving/publishing the report. As soon as the report gets refreshed, the “Today” element gets allocated the corresponding new element of the source column in the background. In this example, the today’s date:
This new calculated column field should just be used within the slicer visual as it looses all the native date functionalities. In the rest of the report you can keep using the original date column.
Dynamic Time Windows
The relative date filter option also provides a convenient way to look back or forth based on today’s date. However, at the time of this writing there isn’t a way to divide the view for looking into both directions at the same time, for instance, relevant in case of rolling time periods showing actual and forecast months in the same figure. One option to manually add this functionality is to make usage of a what-if parameter for choosing the desired period you want to travel in time.
For the purpose of this demo, I created a simple table with two columns, one holding date values and the other one numerical values serving as the base measure for upcoming calculations. Based on this setup, I then created a new calculated column (Year-Mon) based on the date values just for better readability in the table visualization. Now we come to the interesting part which actually lies in the calculated measures together with the what-if parameter which can be created with the provided default values for this example. Though, the important fields to change for any customizations would be the data type which should be a whole number and the combination of the min/max and increment field. The outcome of this combination will be the elements available to select for the time periods. In this case, 0–20 months:
Once confirmed, a new table gets created having two columns. One column is the parameter itself holding all available values and the second one is the currently selected scalar value. This leads me to the following view of tables and fields:
Before going deeper into the DAX formula of the three new calculated measures required to make the time windows possible, let’s first see how the end result will actually look like. It allows you via slider control to just click and drag the range that you want to look back, forth or even in both directions based on the current date/element (2020–11):
Alright, then let’s first take a closer look into the Last and Next Measures. The idea here is to dynamically narrow down the time window based on the DATESINPERIOD function including its four parameters making it a perfect fit for this scenario.
DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)
To start with, the<dates> parameter gets replaced with the date field while for the <start_date> is used another DAX function called EOMONTH. In this example, returning the last day of the current month by passing on TODAY () and 0 as the parameters. We will look into this function again later on. The <number_of_intervals> parameter then represents the currently selected scalar value of the parameter slicer. To go back in time you just need to add a minus sign in front of the parameter’s value as you can see in line 6 otherwise you will see the next N elements.
Now that we understood the principle, let’s go a step further by providing a view that looks in both directions to the same extent. For this, we can take advantage of the EOMONTH function, in specific of the second parameter <months>. This allows you to specify a number of months before or after the first parameter <start_date>.
EOMONTH(<start_date>, <months>)
The whole magic here is to use the parameter’s value on two places in line 5 and 6. Line 5 defines to go back in time using the EOMONTH function and in line 6 you define to go twice the number of months into the future. I further added one month extra to both values (+1) to include the current month and make it the starting point.
Conclusion
Power BI comes with a great set of features designed to make the user experience as smooth and easy as possible. Yet you always encounter situations where the default functionalities don’t satisfy the given requirements and you need to add a little extra intelligence to your report. The two presented methods will help you in case those requirements concern a more intuitive way for time selections. They can be applied on any attribute in the time dimension and especially the dynamic time window approach offers a great scope for any further use-case-specific customizations.
