Rolling X is a useful feature that enables you to calculate the cumulative total or average of a specific metric over a defined period of time. It is commonly used in financial analysis, sales forecasting, and other data-driven processes.
For example, if X=3, rolling 3 months of sales will be the sum or average of the sales for the past three months, and this period will update each month to include the most recent three months.
In this article, I will walk you through different ways to calculate rolling X months in Power BI, using DAX formulas.
Read to learn more.
Introduction
To calculate rolling X months in Power BI, you need a date column that contains the dates that you want to calculate. Once you have your dates table set up, you can do some great data-based analysis.
Also, you must create a measure for the value you want to calculate using any aggregation function such as SUM, AVERAGE, MIN, MAX
, etc. You will use this measure to calculate the rolling X months value.
In Power BI, you can calculate rolling X months in different ways, using different functions such as the DATESINPERIOD, EARLIER, RANKX, DATESBETWEEN
functions, etc. I will explain each of them.
1. Using the DATESINPERIOD function
You can use the DATESINPERIOD
function to calculate rolling X months by following the steps below:
The first thing to do is to create a measure for the value you want to calculate. This measure will be used to calculate the rolling X months value. For example, if you want to calculate the rolling X months average, you will use the AVERAGE
function.
Average_Sales = AVERAGE(SalesTable[SalesAmount])
After that, create a measure for the rolling X months. To do that, use the following DAX formula for the measure you created for the rolling X months.
Rolling X Months =
CALCULATE (
[Average_Sales],
DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), X, MONTH )
)
Replace [Average_Sales]
with the name of the measure you created in the first step (I used the one I created in this example). Replace X with the number of months you want to include in the rolling calculation.
2. Using the EARLIER function
You can also use the EARLIER
function to create a rolling X months calculation. To do that, follow the below steps:
Create a measure for the value you want to calculate (just like I did earlier). Then create a measure for the rolling X months using the following DAX formula. Use the following DAX formula:
Rolling X Months =
VAR CurrentDate = MAX ( 'Date'[Date] )
RETURN
CALCULATE (
[Your Measure],
FILTER (
ALL ( 'Date' ),
'Date'[Date] >= DATEADD ( CurrentDate, -X, MONTH )
&& 'Date'[Date] <= CurrentDate
)
)
Replace [Your Measure]
with the name of the measure you created in step 1, and replace X with the number of months you want to include in the rolling calculation.
The first line defines a variable called CurrentDate. You can replace it in this formula with any date column or variable that contains the current date you want to use as a reference point for the rolling X months period.
NB: The EARLIER
function is not used in the above formula, but can be used in a similar context to reference a previous value.
3. Using the DATESBETWEEN function
To use the DATESBETWEEN
function, create a measure for the value you want to calculate, and create a measure for the rolling X months using the following DAX formula:
Rolling X Months =
CALCULATE (
[Your Measure],
DATESBETWEEN (
'Date'[Date],
DATEADD ( LASTDATE ( 'Date'[Date] ), -X, MONTH ),
LASTDATE ( 'Date'[Date] )
)
)
Replace [Your Measure]
with the name of the measure you created in step 2, and replace X
with the number of months you want to include in the rolling X.
4. Using the RANKX function
Finally, you can use the RANKX
function to create a rolling X months calculation through the following steps:
Create a measure for the value you want to calculate, and then create a measure for the rolling X months using the following DAX formula:
Rolling X Months =
VAR SelectedDate = MAX ( 'Date'[Date] )
VAR DatesRanked = RANKX ( ALL ( 'Date' ), 'Date'[Date],, ASC )
VAR RollingDates =
FILTER (
ALLSELECTED ( 'Date' ),
DatesRanked >= DatesRanked - X
&& DatesRanked <= DatesRanked
)
RETURN
CALCULATE (
[Your Measure],
'Date'[Date] IN RollingDates,
ALLSELECTED ( 'Date' )
)
Replace [Your Measure]
with the name of the measure you created in step 1, and replace X
with the number of months, you want to include in the rolling calculation.
Conclusion
Calculating rolling X months in Power BI is a powerful technique that allows you to explore data trends and patterns over a specified period of time, and there are multiple ways to calculate it depending on the data and the specific requirements.
The DATESINPERIOD, EARLIER,
and RANKX
functions can all be used to calculate rolling X months, and each formula has its advantages and limitations.
By using one of these formulas, you can create a rolling X months calculation that fits your needs and helps you gain insights from your data.
I hope you understood these steps.
Thanks for reading!