Not Getting proper result for 12M moving average

[siddhantk989]

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 tried solutions fom both of the below 2 posts but not getting results from them as well.

http://community.powerbi.com/t5/Desktop/Getting-monthly-average-instead-of-moving-12-month-average-i...

http://community.powerbi.com/t5/Desktop/Past-12-Months-sales-vs-rolling-average/m-p/184757

  Below is the link to the sample data and pbix file on which I am working.

Data Set

Pbix file

any kind of help will be great.

Thanks,

Siddhant

[v-yulgu-msft]

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]

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 · Accepted solution]

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]

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