Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KrisD
Helper II
Helper II

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. 

 

släng.PNG

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

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

vyangliumsft_0-1642060070082.png

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.

vyangliumsft_1-1642060070086.png

 

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

 

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

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

vyangliumsft_0-1642060070082.png

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.

vyangliumsft_1-1642060070086.png

 

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

 

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors