Moving average of a Measure

[Anonymous]

Hi,

I'm trying to add moving average to a measure i created,

but the moving average should ignore blank rows.

for example:

Date             Value

1/1/2017         10

1/2/2017          5

1/3/2017

1/4/2017          8

The moving average 3 (last 3 days) of 1/4/2017 should be (8+5+10)/3 = 7.666

What i have now is, for moving average 3 of 1/4/2017 : (8+5)/2 = 6.5

How can i filter the blank rows before the calculation of the average?

This is the DAX code of the measure:

MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
[DiffFirstSecondAvgOHLC])

DimDate includes all the dates from 1/1/2016 till now.

DiffFirstSecondAvgOHLC is a measure that subtracts two other measures, that don't have values for 1/3/2017 for example.

The dates that don't have value are not trading days, i have a boolean column [IsTradingDay].

Any help will be appriciated.

Thanks,

Eran

[Anonymous]

Hi eranmn

Can I ask why you are using AVERAGEX to calculate moving average?

Best

Martin

[Anonymous]

Hi,

I read it in another forum.

What is your suggestion?

Eran

[Anonymous]

I saw in your earlier post that you want it based on days. So here is the example:

First you need to calculate the average of your target: Average of target = AVERAGE(Table[Target])

Then you calculate the moving average like this: Moving average of target = CALCULATE([Average of target];DATESINPERIOD(Table[Date];LASTDATE(Table[Date]);-3;DAY))

This is without a time dimension. You could also do this with a time dimension. It makes no difference.

I've attached a picture with the solution. Let me know if you want me to elaborate on anything.

Best

Martin

[Anonymous]

Thanks for the example,

but my problem is more complicated,

i need to create moving average to measure, not column,

and the dates column including rows that should be filtered before the average taking place.

[Anonymous]

Can you elaborate? As far as you explain for now, my solution will work for you. Maybe you can provide an example for clarification?

[Anonymous]

You can check my original query, i wrote example,

plus, look at this article:

http://sqlblog.com/blogs/alberto_ferrari/archive/2011/01/26/powerpivot-stocks-exchange-and-the-movin...

This is exactly my problem, but i have measure instead of calculated column.

Eran

[Anonymous]

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. 

[Anonymous · Accepted solution]

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

[Anonymous]

@Anonymous

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

[vanessafvg]

@Anonymous isn't it better practice to use a time dimension though when using date functions?  

[Anonymous]

@vanessafvg Yes, I agree. I always use it myself and I would always advice people to use time dimensions. The note was simply to emphasis that I didn't use it in the test-example. 

[Anonymous]

@Anonymous

Is my solution working for you?

[Anonymous]

I suggest you use calculate instead. I'll make an example for you but first i need to know one thing: Do you want to make moving average based on day, week, month, quarter or something else?

Bestt

Martin

[Anonymous]

Thanks,

On day.

[vanessafvg]

@Anonymous 

which part is blank?

not sure if this will work but give it a bash

MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
not(blank([DiffFirstSecondAvgOHLC])))

[Anonymous]

Hi, 

Thanks for your answer, but unfortunately it's not working,

the blank function ahould be IsBlank, but the UI gives error :

"The function AVERAGEX takes an argument that evaluates to numbers or dates and cannot work with values of type Boolean."

Eran

[vanessafvg]

@Anonymous actually there is blank() and isblank

probably best to add filter to it,

MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
calculate([DiffFirstSecondAvgOHLC], DiffFirstSecondAvgOHLC >= 0)

or something to that affect

[Anonymous]

I tried:

your suggestion gives error: 

"A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
calculate([DiffFirstSecondAvgOHLC], FILTER(VIXFuturesData, [DiffFirstSecondAvgOHLC] <> 0)))

but it give for every row the value of [DiffFirstSecondAvgOHLC], didn't calculate the average.

[vanessafvg]

sorry 😞

probably best to use filter then, did the one you did work?

MA3_DiffFirstSecondAvgOHLC =
AVERAGEX(
DATESINPERIOD (
'DimDate'[Date],
LASTDATE ('DimDate'[Date] ),
-3,
DAY
),
calculate([DiffFirstSecondAvgOHLC], FILTER(VIXFuturesData, [DiffFirstSecondAvgOHLC] <> 0)))

[Anonymous]

no,

it gives for every row the value of [DiffFirstSecondAvgOHLC], didn't calculate the average.