cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ValubiMartin Senior Member
Senior Member

Re: Moving average of a Measure

I understand. Sorry, I missed this part of your problem. I can see that it will be visually misrepresented.

 

 

So you need to do something else. This method can be applied to both measures and columns.

 

- Create your measure: OriginalMeasure = SomeMeasure

- Create a measure, which defines blanks: MeasureWithBlanks = IF(ISBLANK([OriginalMeasure]);"";[OriginalMeasure])

- Create your moving average measure: OriginalMovingAvg = CALCULATE([OriginalMeasure]; DATESINPERIOD(Table1[Date];LASTDATE(Table1[Date]);-3;DAY))

- Create a measure, which excludes blanks: MovingAvg = IF(ISBLANK([MeasureWithBlanks]);"";[Moving average1 of Value])

 

This looks a little silly if you plot it in a trend-visualization (see picture below), because the measure doesn't calculated anything for the missing day. You can go to format and change the x-axis to continous. This will give a smoother interpretation, but it will misrepresent the x-axis.

 

Let me know if this solves your problem. 

 

Moving average without blanks.png

 

 

Highlighted
eranmn Regular Visitor
Regular Visitor

Re: Moving average of a Measure

Thanks for you answer,

 

I solved it already in different way, but i'll save your solution for any case.

The solution that worked for me is:

adding incremental column to the date, which gives id only to the trading days, and another column which is the id - 3 (for the moving average).

then add a measure:

AVERAGEX(FILTER(ALL(DimDate),[Rank] > MAX(DimDate[RankPrev3]) && [Rank] <= MAX(DimDate[Rank])), [AverageColumn])

 

 

 

Eran

ValubiMartin Senior Member
Senior Member

Re: Moving average of a Measure

@eranmn

 

I'm glad you solved it. Clever way to define which days has values and which hasn't. Could I ask you to choose either one of our solutions as an "accepted solution" for the thread, so we can help people with similar challenges?

 

Best of luck in the future

Martin