topic Re: Comparing with a rolling average. in DAX Commands and Tips

Comparing with a rolling average.

ChiragPatnaik — Sat, 06 Jul 2024 14:53:27 GMT

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?

Re: Comparing with a rolling average.

rajendraongole1 — Sat, 06 Jul 2024 15:02:38 GMT

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

Re: Comparing with a rolling average.

jdbuchanan71 — Sat, 06 Jul 2024 15:17:23 GMT

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

Re: Comparing with a rolling average.

ChiragPatnaik — Sat, 06 Jul 2024 15:57:28 GMT

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.

Re: Comparing with a rolling average.

rajendraongole1 — Sat, 06 Jul 2024 16:02:09 GMT

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:

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

Re: Comparing with a rolling average.

rajendraongole1 — Sat, 06 Jul 2024 16:12:04 GMT

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.

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

Re: Comparing with a rolling average.

jdbuchanan71 — Sat, 06 Jul 2024 16:16:21 GMT

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

Re: Comparing with a rolling average.

ChiragPatnaik — Sat, 06 Jul 2024 18:09:41 GMT

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.