Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Moving average of a Measure

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

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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