Comparing with a rolling average.

[ChiragPatnaik]

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?

[jdbuchanan71]

@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]

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]

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!!

[jdbuchanan71]

@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]

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 · Accepted solution]

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!!

[ChiragPatnaik]

HI, 

I'm not sure, why this is not working. The DAX validator is throwing an error. 

I tried the flag expression, But I can't use it as a filter. Only in the Values area.