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

Dear MartynRamsden and amitchandak 

Thanks a lot for your answers. 

I'm getting the same result with both suggestions:

 

The total that I'm getting at the botom looks like the average of the first column. However the month values don't look right at all.

MartynRamsden's avatar
MartynRamsden
Solution Sage
6 years ago

Hi setis 

 

I'm pretty sure the problem here is the [Contract Assets LM] measure as it is being computed in the wrong filter context.

Please try the measure below. I haven't had chance to test it so can't be sure it will work:

 

Avrg Invoiced of CA 12M =
CALCULATE (
    AVERAGEX (
        ADDCOLUMNS (
            ADDCOLUMNS (
                VALUES ( Calendar[MonthYear] ),
                "@Invoiced", [Invoiced],
                "@ConAssLM", [Contract Assets LM]
            ),
            "@InvoicedOfCA", DIVIDE ( [@Invoiced], [@ConAssLM] )
        ),
        [@InvoicedofCA]
    ),
    DATESINPERIOD ( Calendar[Date], LASTDATE ( Calendar[Date] ), -12, MONTH )
)

 

If this doesn't work, please provide a copy of your pbix, excluding any sensitive data and I'll take another look.


Cheers.


Best regards,

Martyn

 

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