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

22 Replies

  • vanessafvg's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        vanessafvg
        Icon for Community Champion rankCommunity Champion
        9 years ago

        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's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi eranmn

     

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

     

    Best

    Martin

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi,

       

      I read it in another forum.

      What is your suggestion?

       

       

       

      Eran

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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