Forum Discussion

IMett's avatar
IMett
Helper III
6 years ago
Solved

Moving average / moving variance

Hi,
I want to calculate a moving average of my measure over the last 12 months. 

I use the following DAX formula so far, which works, as long as my time granularity level is the month. 

 

AVG_Produktion =
CALCULATE (
    [PRODUKTION];
    DATESINPERIOD (
        Datumstabelle[Date];
        LASTDATE ( Datumstabelle[Date] );
        -12;
        MONTH
    )
) / 12
    * DIVIDE ( [PRODUKTION]; [PRODUKTION] )

 

 

The results look like this:

The value for Jan 2022 is correct (average of the months Feb 2021 - Jan 2022), however the value for the whole year 2022 is obviously not correct - I'm actually not completely sure, where this value comes from.
What I want to see here is the sum of all the MA values for 2022, so that I can compare it to the actual yearly production.
Similarly, if I go down on the date level, I want to know, what the average daily production was in the last 12 months.

 

 

 

 

I assume, there should be some kind of an elegant solution using the average or averagex function.

The other question is how to compute a moving variance for the same time periods, ideally without having to calculate the variance formula by hand, bur rather using the VARX.P function.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    You are mixing 2 different measures. One is the 12-month average and the other is the sum of the 12-month averages. If you want to have these 2 morphed into one... then you'll need to write a SWITCH which will select the measure based on what time period is in scope.

     

    Actually... on reflection, you can do something simpler: you can write a measure that will sum up the averages over the currently visible pieces of time. If you never go below the month level, then it's even easier. Hide the individual dates so that they can't be selected, leave only pieces of time from the month up and write:

     

    sumx(
        VALUES ( Calendar[YearMonth] ),

        [12M Average]

    )

     

    This will give you what you want.


    Best
    D

6 Replies