How to compare dates in power bi

UPDATE 2020-11-10: You can find a more complete detailed and optimized example for the following scenario in the DAX Patterns: Comparing different time periods article+video on daxpatterns.com.

Time intelligence calculations are among the most required functionalities in any data model. Year-to-date, same period last year, comparison of different time periods are probably the most requested features of any BI solution.

There is an interesting calculation that requires a certain level of effort in data modeling. Can I compare two custom time periods letting the user choose those periods with a slicer, both in terms of start and duration?

A picture is worth a thousand words. In the next report we compare August 2009 (Sales Amount) with the full year 2008 (Previous Sales). Because the two periods have a different duration, we normalized the values of 2008 using a factor that makes the two numbers comparable:

These are the steps required in order to solve the above scenario:

1. Building a model where the user can choose two different periods using a slicer,
2. Writing a DAX formula to compute the same value over both periods,
3. Normalizing the values using a normalization factor.

The first step requires a new Date table in the model. Using calculated tables, this is as easy as creating a new calculated table that is a shallow copy of the original Date:

Now that you have the table, you need to setup the relationships. If there is only one fact table, you can simply build a new relationship with the proper date in the fact table. If there are multiple fact tables, then the number of inactive relationships grows and the model becomes messy. You can avoid creating multiple inactive relationships by creating only one, although it is less intuitive.

You can link Date with Previous Date through a 1:1 inactive relationship. When you compute values slicing by Date, the relationship is not going to affect your calculations. When you compute values over the previous period, you enable the relationship so that Date becomes filtered by Previous Date.

The resulting model is:

You need to perform ALL on Date to get rid of any existing filter(s) on the Date table. Then, activate the relationship that will filter Date by Previous Date. The effect of these two filter arguments is to move the filter from Previous Date to Date. Because of that, all the fact tables linked to Date will be filtered according to Previous Date.

Using this measure, you can show two measures pertaining to the two selected periods within the same report. Yet, this is not perfect, because the value displayed for Previous Sales is much larger than the one displayed for Sales Amount. The reason is the disproportionate time period – not a decrease in sales. Indeed, we are considering one year for Previous Sales and only one month for Sales Amount.

You need a suitable normalization factor to normalize the values. There might be many, and this is a business decision more than a technical decision. In this example, we go for a simple normalization based on the number of days. If you divide sales by the number of days in the Previous Period selection, you obtain the average sales per day in the previous period. This value, multiplied by the number of days in the Current Period (the Date table) produces a normalized value for the previous selection:

The final formula for the Previous Sales measure becomes:

Once the normalization factor comes into play, the numbers become comparable and you can use them in a matrix or in a chart. You saw a chart example in the first figure of this article. The following example shows the result in a matrix:

Conclusion

DAX offers many time intelligence calculations, but nothing beats your imagination and the power of a custom data model with a bit of DAX code. Users love the flexibility of this calculation, which simply requires users to define a normalization factor.

Insert your email address and press Download for access to the files used in this article.

LinkedIn Twitter Facebook Email

Alberto started working with SQL Server in 2000 and immediately his interest focused on Business Intelligence. He and Marco Russo created sqlbi.com, where they publish extensive content about Business Intelligence. Alberto published several books about Analysis Services, Power BI, and Power Pivot. He is a Microsoft MVP and he earned the SSAS Maestro title, the highest level of certification on Microsoft Analysis Services technology. Today, Alberto's main activities are in the delivery of DAX and data modeling workshops for Power BI and Analysis Services all around the world. Alberto offers consulting services on large and complex data warehouses to provide assessments and validation of project analysis or to perform specific problem-solving activities. Alberto is a well-known speaker at many international conferences, like PASS Summit, Sqlbits, and Microsoft Ignite. He loves to be on stage both at large events and at smaller user groups meetings, exchanging ideas with other SQL and BI fans. When traveling for work, he likes to engage with local user groups to provide evening sessions about his favorite topics. Thus, you can easily meet Alberto by looking up local Power BI user groups during scheduled courses. Outside of SQLBI, most of Alberto's personal time is spent practicing video games, in the vain hope of eventually beating his son.

Accept cookies to show comments. Cookies Policy
Allow all cookies