Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi!
I'm trying to do a moving average based on the last N rows but nothing I've tried seems to work.
When I do the average calculation I'm trying to use the current row minus N rows, but the code does not consider the time-part of the timestamp, only the day (in this case the 16th).
How can I get the full timestamp into the calculation in order to get the moving average to work?
The DAX:
As you can see in the image below, the MAX considers the row on a day level.
Solved! Go to Solution.
Hi @KrisD ,
Here are the steps you can follow:
Create measure.
Wagon Ore Normal Load AVG MA7 timestamp =
var _1=MAX('Table'[TRAIN_CYCLE_TIMESTAMP])
return
IF(
not ISBLANK(MAX('Table'[Wagon Ore Normal Load AVG])),
AVERAGEX(
FILTER(
SUMMARIZE(ALLSELECTED('Table'),
'Table'[TRAIN_CYCLE_TIMESTAMP],
"Avg value",AVERAGE('Table'[Wagon Ore Normal Load AVG])),
'Table'[TRAIN_CYCLE_TIMESTAMP]<=_1),
[Avg value])
)
Max timestamp = MAX('Table'[TRAIN_CYCLE_TIMESTAMP])
Generally speaking, the current value displayed by Max(), the format is also the current value, you can select [Max timestamp] - Measure tools - Fromat - to change to the format in the picture.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @KrisD ,
Here are the steps you can follow:
Create measure.
Wagon Ore Normal Load AVG MA7 timestamp =
var _1=MAX('Table'[TRAIN_CYCLE_TIMESTAMP])
return
IF(
not ISBLANK(MAX('Table'[Wagon Ore Normal Load AVG])),
AVERAGEX(
FILTER(
SUMMARIZE(ALLSELECTED('Table'),
'Table'[TRAIN_CYCLE_TIMESTAMP],
"Avg value",AVERAGE('Table'[Wagon Ore Normal Load AVG])),
'Table'[TRAIN_CYCLE_TIMESTAMP]<=_1),
[Avg value])
)
Max timestamp = MAX('Table'[TRAIN_CYCLE_TIMESTAMP])
Generally speaking, the current value displayed by Max(), the format is also the current value, you can select [Max timestamp] - Measure tools - Fromat - to change to the format in the picture.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
@KrisD , Create a rank column and try
Rank = rankx('F WAGON CYCLE' ,'F WAGON CYCLE'[TRAIN_CYCLE_TIMESTAMP],,asc,dense)
Wagon Ore Normal Load AVG MA7 timestamp =
IF (
NOT ISBLANK ( [Wagon Ore Normal Load AVG] ),
CALCULATE ( [Wagon Ore Normal Load AVG],
FILTER (
ALLSELECTED ( 'F WAGON CYCLE' ),
'F WAGON CYCLE'[Rank] <= MAX ( 'F WAGON CYCLE'[Rank] ) &&
'F WAGON CYCLE'[Rank] >= MAX ( 'F WAGON CYCLE'[Rank] ) -2 ) //The minus N makes no difference
)
)
Hi and thanks for your response!
I have on purpose excluded the use of calculated columns in this case. In case I get no solution ideas with the use of just a measure I'll consider using your rank method.
I'll accept this as solution in case nothing new comes up in a couple of days, but would of course like to know why for instance MAX or SELECTEDVALUE do not return the complete timestamp and how this can be achieved.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |