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 invoiced amount of the current month divided by the CA of the previous month.  

 

I've tried the following: 

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

 

This is the result I'm getting:

 

I'm not sure if this is giving me the rolling average per day or something else. What I need is the rolling average per month of the last 12 months. 

 

For reference, my calendar table looks like this:

 

Could somebody let me know what's wrong?

 

Thanks a lot!

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
    )
)

9 Replies

  • MartynRamsden's avatar
    MartynRamsden
    Solution Sage
    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
    Super User
    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.