Forum Discussion

KrisD's avatar
KrisD
Icon for Helper II rankHelper II
4 years ago
Solved

How to use timestamp in moving average calculation

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. 

 

Need Help DAX measure
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      KrisD
      Icon for Helper II rankHelper II
      4 years ago

      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.  

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Darnok_3993
      New Member
      1 year ago

      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