In a previous blog, I explained how to make calculations like year-to-date (YTD) via DAX. But what if you want to know each month's total of, say, the last three months? In this blog, I explain how to calculate cumulative values in Power BI via the DATESINPERIOD function.
Initial situation
We start with a simple table. In the table below you can see the sales achieved in each month.
DAX formula
Below you can find the formula I use to calculate in each month the sales of the past three months.
I start by creating the variable DATES. This variable should contain the full set of dates for the past three months. You can achieve this by using the DATESINPERIOD formula. This formula has four parameters:
The first parameter is a reference to the column that contains all your dates in your data model. In this case, that's "Date"[Date].
The second parameter is the start date of the set of dates you want to create. For this calculation, that is the last date in the current filter context.
In the third parameter you specify how many intervals you want forwards or backwards. For this calculation it is -3. This is because we want to get a range of three months as seen from the last date.
This immediately gives away the last parameter, what kind of interval to work with: "Month".
If all goes well, the variable will now contain a range of the last three months in each filter context.
Then I calculate through the CALCULATE function, with which I will override the current filter context, what the turnover is for the range contained in variable DATES.
Final situation
Adding the new calculation to the table gives the situation below. For each month, the turnover for the past three months is now visible.
Conclusion
I hope this blog has helped you get a better understanding of how to use DATESINPERIOD to calculate the cumulative values of a self-selected time window. If you are interested in our other blogs you can find them here.