How to use timestamp in moving average calculation

[KrisD]

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. 

[Anonymous · Accepted 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

[Darnok_3993]

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

[amitchandak]

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

[KrisD]

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.