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] )

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.

 

  • ChandeepChhabra's avatar
    ChandeepChhabra
    Impactful Individual
    7 years ago

    Anonymous

    I am not sure if I have understood your question correctly.

     

    When you say remove the first 2 months - You mean still want the rolling total to start from Jan but don't want to show Jan and Feb in horizontal axis of the chart ?

     

    Can you please elaborate ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      ChandeepChhabra

       

      Well what i want is to show a good calculation of the rolling average. With my formula the calculation only begin in march, so the rolling haverag is equal to march value. For April the calulation is (march + april)/2 wich is still incorect.

       

      To have what i want i need to begin my measure at this date (2015;10;1) to have the rolling average for january. So is it possible to hide 2015?

       

      Thank you for your precious help

      Martin