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 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-in-a/m-p/188079#M82693

 

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

 

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

4 Replies

  • 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