Forum Discussion

ChiragPatnaik's avatar
ChiragPatnaik
Regular Visitor
2 years ago
Solved

Comparing with a rolling average.

I am looking to create a rolling average of volume for each symbol. and then set a flag if today's/(the day's) volume for the symbol is higher than this average. 

This is what I have right now.

 

 

=CALCULATE(
    AVERAGE(Delivery[TTL_TRD_QNTY]),GROUPBY(Delivery,Delivery[SYMBOL]),
       FILTER(
        ALL(Delivery),
        Delivery[DATE1]<=MAX(Delivery[DATE1]) && Delivery[DATE1] >= MAX(Delivery[DATE1])-35
    )
)

 

 

Once I get this in the calculated filed, then it should be a trivial matter to add another field with the flag with a if statement.  

I seem to have got the first part right by using GroupBy as you can see in this. 

But when I try to compare that in a new column to set a flag, I get an error. 

 

 

=IF(Delivery[TTL_TRD_QNTY]>Delivery[SMV],"Up","Down")

 

 

 

Is there any way to overcome this? Or a different method perhaps?

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi ChiragPatnaik - Not sure , why my solution is disappeared for this question, again posting on the same thread. please check the below measure for rolling ag. and flag.

     

    RollingAvgVolume =
    CALCULATE(
    AVERAGE(Delivery[TTL_TRD_QNTY]),
    FILTER(
    ALL(Delivery),
    Delivery[SYMBOL] = MAX(Delivery[SYMBOL]) &&
    Delivery[DATE1] <= MAX(Delivery[DATE1]) &&
    Delivery[DATE1] > MAX(Delivery[DATE1]) - 35
    )
    )

     

    create a flag measure as below:

    VolumeFlag =
    IF(
    MAX(Delivery[TTL_TRD_QNTY]) > [RollingAvgVolume],
    "Up",
    "Down"
    )

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

7 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    2 years ago

    ChiragPatnaik 

    You are almost always better off doing a measure rather than a calculated column because the column only recalculates when the model is refreshed.  Does this measure give you to rolling average you are expecting?

    Rolling Average =
VAR _MaxDate = CALCULATE ( MAX ( Delivery[DATE1] ), ALL ( Delivery[DATE1] ) )
VAR _Start = _MaxDate - 35
RETURN
    CALCULATE (
        AVERAGE ( Delivery[TTL_TRD_QNTY] ),
        DATESBETWEEN ( Delivery[DATE1], _Start, _MaxDate )
    )

     

    • ChiragPatnaik's avatar
      ChiragPatnaik
      Regular Visitor
      2 years ago

      Unfortunately, this is not working. 

      SMA36 is your formula. SMA362 is the one I posted. 

       

      The one you posted, is not moving by day. 


      Incidentally, the one I posted is also saved as a measure. 

       

      • rajendraongole1's avatar
        rajendraongole1
        Super User
        2 years ago

        Hi ChiragPatnaik - Not sure , why my solution is disappeared for this question, again posting on the same thread. please check the below measure for rolling ag. and flag.

         

        RollingAvgVolume =
        CALCULATE(
        AVERAGE(Delivery[TTL_TRD_QNTY]),
        FILTER(
        ALL(Delivery),
        Delivery[SYMBOL] = MAX(Delivery[SYMBOL]) &&
        Delivery[DATE1] <= MAX(Delivery[DATE1]) &&
        Delivery[DATE1] > MAX(Delivery[DATE1]) - 35
        )
        )

         

        create a flag measure as below:

        VolumeFlag =
        IF(
        MAX(Delivery[TTL_TRD_QNTY]) > [RollingAvgVolume],
        "Up",
        "Down"
        )

         

        Did I answer your question? Mark my post as a solution! This will help others on the forum!
        Appreciate your Kudos!!

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    2 years ago

    ChiragPatnaik 

    To get it to move by day you just need to adjust it a bit.  Sorry, I was thinking you wanted the current 35 days always.

    Rolling Average = 
VAR _MaxDate = MAX ( Delivery[DATE1] )
VAR _Start = _MaxDate - 35
RETURN
    CALCULATE (
        AVERAGE ( Delivery[TTL_TRD_QNTY] ),
        DATESBETWEEN ( Delivery[DATE1], _Start, _MaxDate )
    )
  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi ChiragPatnaik - Create two measures one for rolling average and then use another measure for the flag.

    RollingAvgVolume =
    CALCULATE(
    AVERAGE(Delivery[TTL_TRD_QNTY]),
    FILTER(
    ALL(Delivery),
    Delivery[SYMBOL] = MAX(Delivery[SYMBOL]) &&
    Delivery[DATE1] <= MAX(Delivery[DATE1]) &&
    Delivery[DATE1] > MAX(Delivery[DATE1]) - 35
    )
    )

     

    another measure for flag:

    VolumeFlag =
    IF(
    MAX(Delivery[TTL_TRD_QNTY]) > [RollingAvgVolume],
    "Up",
    "Down"
    )

     

    Hope it works

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi ChiragPatnaik -can you try below measure for rolling average as a measure and then use another measure for the flag as beloe 

     

    Measure 1:

    RollingAvgVolume =
    CALCULATE(
    AVERAGE(Delivery[TTL_TRD_QNTY]),
    FILTER(
    ALL(Delivery),
    Delivery[SYMBOL] = MAX(Delivery[SYMBOL]) &&
    Delivery[DATE1] <= MAX(Delivery[DATE1]) &&
    Delivery[DATE1] > MAX(Delivery[DATE1]) - 35
    )
    )

    Measure 2:

    another measure for flag

    VolumeFlag =
    IF(
    MAX(Delivery[TTL_TRD_QNTY]) > [RollingAvgVolume],
    "Up",
    "Down"
    )

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!