In een eerdere blog heb ik uitgelegd hoe je berekeningen als year-to-date (YTD) kunt maken via DAX. Maar wat als je iedere maand het totaal van bijvoorbeeld de laatste drie maanden wilt weten? In deze blog leg ik je uit hoe je cumulatieve waarden berekend in Power BI via de DATESINPERIOD functie.
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.