topic Re: How to use timestamp in moving average calculation in DAX Commands and Tips

How to use timestamp in moving average calculation

KrisD — Mon, 10 Jan 2022 10:52:10 GMT

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:

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'[TRAIN_CYCLE_TIMESTAMP] <= MAX ( 'F WAGON CYCLE'[TRAIN_CYCLE_TIMESTAMP] ) &&

'F WAGON CYCLE'[TRAIN_CYCLE_TIMESTAMP] >= MAX ( 'F WAGON CYCLE'[TRAIN_CYCLE_TIMESTAMP] ) -2 ) //The minus N makes no difference

)

As you can see in the image below, the MAX considers the row on a day level.

Re: How to use timestamp in moving average calculation

amitchandak — Mon, 10 Jan 2022 11:09:49 GMT

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

Re: How to use timestamp in moving average calculation

KrisD — Mon, 10 Jan 2022 11:46:00 GMT

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.

Re: How to use timestamp in moving average calculation

Anonymous — Thu, 13 Jan 2022 07:48:58 GMT

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

Re: How to use timestamp in moving average calculation

Darnok_3993 — Fri, 15 Nov 2024 14:21:13 GMT

Hello,

I use the following solution, since I can declare how many timestamps/connected values I want to use for the moving average. It also considers the time part in the time/date formatted date column.

Moving Average =

VAR CurrentDate = MAX(DataTable[StartDate])

VAR PreviousDates =

FILTER( ALLSELECTED(DataTable),

DataTable[StartDate] <= CurrentDate

)

VAR RankedDates =

ADDCOLUMNS(

PreviousDates,

"Rank", RANKX(PreviousDates, DataTable[StartDate], , DESC, Dense)

)

VAR FilteredDates =

FILTER(

RankedDates,

[Rank] <= 3

)

VAR SumValues =

SUMX(

FilteredDates,

DataTable[Value]

)

VAR CountValues =

COUNTROWS(FilteredDates)

RETURN

IF(

CountValues > 0,

DIVIDE(SumValues, CountValues)

)

Best regards

KW