Forum Discussion

siddhantk989's avatar
siddhantk989
Helper III
9 years ago
Solved

Not Getting proper result for 12M moving average

Hi All,      I have been working on 12month running average from long time and I am not able to figure out why I am getting some random values instead of getting my 12M rolling average.     I tri...
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi siddhantk989,

     

    In your scenario, you need to calculated the monthly total first. Please modify the measure [Rolling Average measure] as below:

    Monthly total =
CALCULATE (
    SUM ( MasterData[Actual Sales] ),
    ALLEXCEPT (
        MasterData,
        MasterData[GL Date].[Year],
        MasterData[GL Date].[Month]
    )
)

Rolling Average measure =
DIVIDE (
    CALCULATE (
        [Monthly total],
        FILTER (
            ALL ( MasterData ),
            MasterData[GL Date] > MAX ( MasterData[12 month ago] )
                && MasterData[GL Date] <= MAX ( MasterData[GL Date] )
        )
    ),
    12
)

     Regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi siddhantk989,

 

Based on my original post in this old thread: Getting monthly average instead of moving 12 month average in a line chart, rather than creating calculated column to generate the moving average, you could create measures like below:

 

Rolling Average measure = 
DIVIDE (
    CALCULATE (
        SUM ( MasterData[Actual Sales] ),
        FILTER (
            ALL ( MasterData ),
            MasterData[GL Date] > max ( MasterData[12 month ago] )
                && MasterData[GL Date] <= MAX( MasterData[GL Date] )
        )
    ),
    12
)

Lastyear-date =
DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), 1 )

lastmonth-date =
IF (
    MONTH ( TODAY () ) = 1,
    DATE ( YEAR ( TODAY () ) - 1, 12, 1 ),
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
)

Moving 12M average =
CALCULATE (
    MasterData[Rolling Average measure],
    FILTER (
        MasterData,
        MasterData[GL Date] >= MasterData[Lastyear-date]
            && MasterData[GL Date] <= MasterData[lastmonth-date]
    )
)

Best regards,
Yuliana Gu

siddhantk989's avatar
siddhantk989
Helper III
9 years ago

Hi v-yulgu-msft

 

Thanks for replying. Even by creatign measures instead of columns it stil does not works. I can still see some random values poping up isntead of 12M average.

 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi siddhantk989,

     

    In your scenario, you need to calculated the monthly total first. Please modify the measure [Rolling Average measure] as below:

    Monthly total =
CALCULATE (
    SUM ( MasterData[Actual Sales] ),
    ALLEXCEPT (
        MasterData,
        MasterData[GL Date].[Year],
        MasterData[GL Date].[Month]
    )
)

Rolling Average measure =
DIVIDE (
    CALCULATE (
        [Monthly total],
        FILTER (
            ALL ( MasterData ),
            MasterData[GL Date] > MAX ( MasterData[12 month ago] )
                && MasterData[GL Date] <= MAX ( MasterData[GL Date] )
        )
    ),
    12
)

     Regards,
    Yuliana Gu

    • siddhantk989's avatar
      siddhantk989
      Helper III
      9 years ago

      Hi v-yulgu-msft

       

        Sorry for the late reply, i was actually stuck with some other work but thansk a lot for helping. The solution is working fine now.

       

      Thanks,

      Siddhant