Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Quick measure - Moving average

Hello,   I'm new with power BI and i have trouble calculating moving average.   I use the quick measure option to calculate 3 month moving average and i get this result:         It seems...
Need Help
Show and Tell
Tips and Tricks
  • ChandeepChhabra's avatar
    ChandeepChhabra
    7 years ago

    You can write something like

    =IF( [OtherMeasureInChart]<>Blank(), [Rolling Avg Measure] )

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks for your answer,

 

I've found a solution to my problem, for a 3 month rolling average i have to write "-2" instead of "-3".

 

I have now another question.

 

You can see in my chart that rolling average is calculated for future month (January, February and March 2019). How can i stop the measure so that i don't have a wrong presentation?

 

Thanks

Martin

ChandeepChhabra's avatar
ChandeepChhabra
Impactful Individual
7 years ago

You can write something like

=IF( [OtherMeasureInChart]<>Blank(), [Rolling Avg Measure] )

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you for your answer, that's work perfectly.

     

    I have a last question, is it possible to remove the first 2 month of 2016 on the chart for the measure because this is not calculated on a 3 month basis due to filter applied on the report.

     

    Thank you

    Martin

    • ChandeepChhabra's avatar
      ChandeepChhabra
      Impactful Individual
      7 years ago

      Hi Anonymous

      You can further wrap the entire IF function in the CALCULATE Function. Something like


      =CALCULATE (
      [EntireIFfunctionMeasure],
      DATESBETWEEN ( Cal[Date], DATE ( 2016, 3, 1 ), MAX ( Cal[Date] ) ) )

       

      Cal[Date] - will come from your calendar table. Should be related to your transactions table

      DATESBETWEEN Function - will allow you to run the calculation between set time periods. In this case the start date has been manually punched as 1st Mar 2016 by using the DATE function

       

      I hope that works

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi,

         

        Thanks for your answer. It's not working as the rolling average for march is equal to march data. The calculation seems to begin in march 2016.

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Your simple trick saved my day..

    Thanks mate