Solved: Moving average of a Measure - Page 3

Anonymous · ‎07-17-2017

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 · ‎07-18-2017

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

Moving average of a Measure