Forum Discussion

setis's avatar
setis
Post Partisan
6 years ago
Solved

Rolling average

Dear experts,   I am trying to calculate the rolling average of the last 12 months of the following measure:   Invoiced of CA = DIVIDE( [Invoiced];[Contract Assets LM] ; 0)   This is the inv...
Need Help
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    Hi setis 

     

    You're almost there but your AVERAGEX is iterating over Calendar[Date], I think this needs to be Calendar[Month].

    Try this:

     

    Avrg Invoiced of CA 12M =
CALCULATE (
    AVERAGEX (
        VALUES ( Calendar[Month] );
        [Invoiced of CA]
    );
    DATESINPERIOD (
        Calendar[Date];
        LASTDATE ( Calendar[Date] );
        -12;
        MONTH
    )
)

     

    Best regards,

    Martyn

     

    If I answered your question, please help others by accepting it as a solution.

  • amitchandak's avatar
    amitchandak
    6 years ago

    Values is used to use distinct values. Means if should group at day level

    Avrg Invoiced of CA 12M = 
CALCULATE(
    AVERAGEX(VALUES(Calendar[Date]);[Invoiced of CA]) ;
    DATESINPERIOD(
        Calendar[Date];
        LASTDATE(Calendar[Date]);
        -12;
        MONTH
    )
)

    Try

    Avrg Invoiced of CA 12M = 
CALCULATE(
    AVERAGEX(VALUES(Calendar[Month-Year]);[Invoiced of CA]) ;
    DATESINPERIOD(
        Calendar[Date];
        LASTDATE(Calendar[Date]);
        -12;
        MONTH
    )
)
setis's avatar
setis
Post Partisan
6 years ago

MartynRamsden Sure!

 

As you mention the issue might be in the actual measure

 

The one I'm using is:

 

Contract Assets_LM = CALCULATE([Contract Assets_cum] ; PREVIOUSMONTH(Calendar_NAV[Date] ))

 

 

The Contract Assets cumulative is:

 

Contract Assets_cum = 
VAR CumTilCM = 
CALCULATE(
    [Contract Assets_ALL];
    FILTER(
        ALL(Calendar_NAV[Date]);
        Calendar_NAV[Date] <= MAX(Calendar_NAV[Date])))
RETURN
IF(
    MAX(Calendar_NAV[IsCurrentMonth]) = 0 &&
    MAX(Calendar_NAV[FutureDate]) = "Past"; CumTilCM;[CA_CurrentMonth])

 

 

Contract Assets_ALL is a measure with  no particular Calendar filter. 

 

[CA_CurrentMonth] is a bit complicated, but I don't think that it is  affecting the results in this case. 

 

Thanks in advance!

dax's avatar
dax
Community Support
6 years ago

Hi setis, 

If possible, could you please inform me more detailed information(such as your expected output and your sample data, you could use othe data instead of real data)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • setis's avatar
    setis
    Post Partisan
    6 years ago

    Dear MartynRamsden , amitchandak and dax ,

     

    I tried to build a dummy simplified data model and I could not reproduce the issue. 

     

    I just realized that the numbers that I was getting were correct and they looked so weird since they were affected by an internal adjustement on the previous year. 

     

    All in all, the measures and your suggestions were all correct. Thank you very much for your help!