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.

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

      • 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!