DAX measures to calculate same period last month / quarter in Power BI
Understanding the problem
Let’s first clarify our objective before delving into the technical aspects. The goal here is to compare sales amount for a specified period (e.g. from February 18, 2019, to February 21, 2019) by shifting the analysis across months, quarter, and years. See below an example of shifting the period for last month, last quarter, and last year.
The data model
For this example, we are going to use a simplified version of the Contoso database (fact table with around 2 million rows). See below the data model.
DAX measures
First, we need to understand how to calculate the sales for the same period while shifting by X months, quarter, and years. If you are familiar with time intelligence functions in DAX, you might have already heard about the DAX function SAMEPERIODLASTYEAR. But you might be wondering, is there a DAX function for the same period last month / quarter? The answer is no! (at least at the point of this blog post there is not). In fact, you don’t need such a DAX function, if you understand what is happening behind SAMEPERIODLASTYEAR.
We want to calculate the value not just for “SamePeriodLastYear(Month or Quarter)”, but for a given X period (SamePeriodXYear (XMonth or XQuarter)). We want to provide users with dynamic flexibility, allowing them to shift the calculation by X months / quarter / years.
Step 1: Create a table named “period” as numeric range parameter to shift the period dynamically
See below how to create a numeric range parameter in Power BI Desktop.
Step 2: Create the DAX measures and a calculation group
You don’t actually need to create the calculation group to achieve the result in this example, but it becomes useful if you want to perform this calculation for other measures and you want to avoid writing the same code logic over and over. You can create the calculation group either in Power BI Desktop or in Tabular Editor.
See below all the DAX measures created inside the calculation group.
Same Period Month =
VAR _StartDate =
MIN ( 'Dates'[Date] )
VAR _EndDate =
MAX ( 'Dates'[Date] )
VAR _Period =
SELECTEDVALUE ( Period[Period] )
VAR _VTable =
CALCULATETABLE (
DATEADD ( 'Dates'[Date], _Period, MONTH ), // Here you can change to QUARTER or YEAR
'Dates'[Date] >= _StartDate
&& 'Dates'[Date] <= _EndDate
)
VAR _Result =
CALCULATE ( SELECTEDMEASURE (), _VTable )
RETURN
_Result
The internal calculation of SAMEPERIODLASTYEAR is actually calling the DATEADD function. The advantage of using the DATEADD is that it is possible to use not only year but also month and quarter as argument.
Step 3: Check the result in a matrix visual
First of all it is important that you understand the DAX code and then it is time to make sure the calculation is doing what you want.
See below the final result. The 3 matrices below the dashed line are provided for result comparison. As you can see, from the selected date range (18.February 2019 - 21.February 2019) we are getting the desired date range by shifting from the selected “Period”, for example:
“Same Period Month” with a selected “Period” of -1 returns the date range 18.January 2019 - 21.January 2019
“Same Period Quarter” with a selected “Period” of -1 returns the date range 18.November 2018 - 21.November 2018
“Same Period Year” with a selected “Period” of -1 returns the date range 18.February 2018 - 21.February 2018
Conclusion
One crucial point I want to highlight is that you don’t have to depend solely on time intelligence functions when writing your DAX. If you're proficient in DAX, you can always rewrite your code according to your requirements. If you choose to use time intelligence functions, I think it is essential to understand the calculation behind to prevent unexpected results.
I hope you enjoyed this blog post and happy DAX coding :)
Last updated on March 22, 2024