Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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
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...
20230134
20230231
20230353
20230439
20230536

 

But I had expected, and I want to achieve

MonthYear ('D Kalender Previous')Result measure Actuals 12 mnd rullerende snitt...
202301average of actuals for months 202202 to 202301
202302average of actuals for months 202203 to 202302
202303average of actuals for months 202204 to 202303
202304average of actuals for months 202205 to 202304
202305average of actuals for months 202206 to 202305

 

How can I achieve this? I hope that someone can help me.

4 Replies

    • some_bih's avatar
      some_bih
      Community Champion

      Hi Anonymous I will watch and share my findings.

      • some_bih's avatar
        some_bih
        Community 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.