Comparing Two Different Periods in DAX

Comparing different time periods is one of the most commonly required functionalities in models. Time functions such as cumulative sum or the same period last year are often needed in any BI solutions. Additionally, users increasingly express the need for flexibility in formulating comparative periods. How to address this if we want to compare different (even non-identical) time periods using two date slicers?

The following image best describes it:

How to create such a solution step by step?

  1. Create a model where the user can select two time periods.
  2. Create DAX measures to calculate values for both periods.
  3. Bring values to a common denominator.

The first step is to create a calendar table. We can do this in many ways; for the purposes of this exercise, let it be:

Calendar =
CALENDAR(
MIN(financials[Date]),
MAX(financials[Date])
)
The next step is to create an identical calendar table for filtering the compared period:
PrevCalendar =
ALLNONBLANKROW('Calendar')
Then we need to establish relationships between these tables and manually deactivate it. This will ensure that the relationship does not affect calculations until we activate it with a formula. The model should look like the following:
Next, we create measures summing Sales values.

_Current Sales =
SUM(financials[Sales])

_Previous Sales =
CALCULATE([_Current Sales],
ALL(‘Calendar’),
USERELATIONSHIP(
‘Calendar'[Date],PrevCalendar[Date] )
)

We deliberately use the ALL function to remove filters applied to the Calendar table. Then, we activate the relationship between the Calendar and PrevCalendar tables. As a result, we can filter results using the PrevCalendar table. Now, using these two measures, we can show the actual values that occurred in the analyzed period.
The next step is to calculate the change factor to normalize the results. To do this, we use the factor derived from the difference in days between the selected time periods, i.e., dividing the number of days in the main period (G) by the number of days in the comparative period (P). The measure will look like this:
_Factor =
DIVIDE(
COUNTROWS('Calendar'),
COUNTROWS('PrevCalendar')
)
Następnie poprawimy miarę _Previous Sales:
_Previous Sales =
CALCULATE(
[_Current Sales],
ALL('Calendar'),
USERELATIONSHIP(
'Calendar'[Date],PrevCalendar[Date]
)
)*[_Factor]
By using the change factor, the values become comparable. This is illustrated in the screenshot below:

The solution is not difficult to implement. I always plan it when building a data model, and when creating measures, I always ensure that they allow for the same calculations in two selected periods. This saves additional work when implementing a new change later on.

Interesting article? Share further!

Leave A Comment