Calculating rolling average based on a expression and connection with a second data table

Anonymous — Thu, 20 Jul 2023 11:48:50 GMT

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
Result

Now 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.

Re: Calculating rolling average based on a expression and connection with a second data table

some_bih — Wed, 19 Jul 2023 13:25:38 GMT

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/

Re: Calculating rolling average based on a expression and connection with a second data table

Anonymous — Thu, 20 Jul 2023 11:50:17 GMT

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?

Re: Calculating rolling average based on a expression and connection with a second data table

some_bih — Thu, 20 Jul 2023 12:12:33 GMT

Hi Anonymous I will watch and share my findings.

Re: Calculating rolling average based on a expression and connection with a second data table

some_bih — Sun, 23 Jul 2023 17:48:27 GMT

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.

topic Re: Calculating rolling average based on a expression and connection with a second data table in DAX Commands and Tips

Calculating rolling average based on a expression and connection with a second data table

Re: Calculating rolling average based on a expression and connection with a second data table

Re: Calculating rolling average based on a expression and connection with a second data table

Re: Calculating rolling average based on a expression and connection with a second data table

Re: Calculating rolling average based on a expression and connection with a second data table