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

    • setis's avatar
      setis
      Post Partisan
      6 years ago

      MartynRamsden , I get the exact same result as before. 

       

      Thanks a lot for trying. I'll try to prepare a similar scenario with fake numbers to reproduce the issue and I'll post it here. 

      • MartynRamsden's avatar
        MartynRamsden
        Solution Sage
        6 years ago

        Hi setis 

         

        I thought that might be the case but it was worth a shot!

         

        I look forward to seeing your dummy data model.

        In the meantime, are you able to share your DAX expression for the [Contract Asset LM] measure?

         

        Best regards,

        Martyn

         

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