Forum Discussion
Comparing with a rolling average.
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!!
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 )
)
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.
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!!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.