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,

 

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