Forum Discussion
Calculating rolling average based on a expression and connection with a second data table
Hi all,
My data is as follows:
I have 2 Date Tables, one is called 'D Kalender' and the other is called 'D Kalender Previous'. The 'D Kalender Previous' is table which is a copy of 'D Kalender' table. Both tables are connected via *-1 relationship from 'D Kalender' to 'D Kalender Previous', based on [TID_ID] that's inactive.
I've done this because I have build 1 report, with many visuals and some visuals I want to show only based on the selected period (monthyear) and others I want to show from the selected period-13 months to the selected period. For the first kind of visuals I have 'D Kalender[MonthYear]' are tables or cards without x-axis and shows only data based on the selected month or for example YTD calculated by a formula based on the selected date. For the second kind of visuals that are mostly line charts, where I have 'D Kalender Previous[MonthYear] on the x-axis. So that I see for example not only 1 month, but the values of the last 13 months. Based on this video https://www.youtube.com/watch?v=d8Rm7dwM6gc
Now I have a measure to calculate the actuals, called [Actuals], that's a formula that takes the sum of a lot of values. Without filtering on dates. This measure I used for the first kind of visuals. For the second kind of visuals I have use a newly created measure called [Actuals siste 13 månader avhenging av valgt dato] to show the actuals each month. And this works fine!
The measure is as follows:
Actuals siste 13 månader avhenging av valgt dato =
VAR ReferenceDate = max('D Kalender'[Dato]) // selected date
VAR PreviousDates =
DATESINPERIOD(
'D Kalender Previous'[Dato],
ReferenceDate, // fra selected date
-13, // til 13 måneder tilbake
MONTH
)
VAR Result =
CALCULATE(
[Actuals],
REMOVEFILTERS('D Kalender'),
KEEPFILTERS(PreviousDates),
USERELATIONSHIP('D Kalender'[TID_ID], 'D Kalender Previous'[TID_ID])
)
Return
ResultNow I want that to create a new measure which calculates the rolling average for the last 12 months for those values, so that I can add that as a line to the same visual. But when I try to calculate that, I don't get the result I want to achieve.
I've tried now to use the following formule
Actuals 12 mnd rullerende snitt avhanging av actuals siste 2 års =
VAR NumOfMonths = 12
VAR SelectedDate = MAX('D Kalender Previous'[Dato])
VAR Period = DATESINPERIOD(
'D Kalender Previous'[Dato], SelectedDate, -NumOfMonths, MONTH)
VAR StartDate = FIRSTDATE(Period)
VAR Result =
AVERAGEX(Period,
CALCULATE(
[Actuals],
REMOVEFILTERS('D Kalender'),
KEEPFILTERS(Period),
USERELATIONSHIP('D Kalender'[TID_ID], 'D Kalender Previous'[TID_ID]))
)
RETURN
Result
With as result for each MonthYear the values of the actuals in stead of the rolling average for the last 12 months.
So I get now this:
| MonthYear ('D Kalender Previous') | Result measure Actuals 12 mnd rullerende snitt... |
| 202301 | 34 |
| 202302 | 31 |
| 202303 | 53 |
| 202304 | 39 |
| 202305 | 36 |
But I had expected, and I want to achieve
| MonthYear ('D Kalender Previous') | Result measure Actuals 12 mnd rullerende snitt... |
| 202301 | average of actuals for months 202202 to 202301 |
| 202302 | average of actuals for months 202203 to 202302 |
| 202303 | average of actuals for months 202204 to 202303 |
| 202304 | average of actuals for months 202205 to 202304 |
| 202305 | average of actuals for months 202206 to 202305 |
How can I achieve this? I hope that someone can help me.
4 Replies
- some_bihCommunity Champion
Hi Anonymous I would suggest, if you are not already, saw link related to this topic, rolling average as I do not understand your measure (I tried to figure out but no luck :))
One tip based on experience: only have one Date table, otherwise complexity is created sooner or later.
Hope this help.
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
- AnonymousNot applicable
Unfortunately that doesn't work :(. I use the technique with 2 Date tables based on this video https://www.youtube.com/watch?v=d8Rm7dwM6gc. Maybe after you have seen this, you can help me?
- some_bihCommunity Champion
Hi Anonymous I will watch and share my findings.
- some_bihCommunity Champion
Hi Anonymous I still believe that you should follow my suggestion for Average (it could be a lot to rework). Still, if you really want to follow solution you link to youtube, then just follow logic in download section for that article.
It could be that I do not helping you, but model is really everything in DAX / power bi solutions.