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
009co
Helper IV
Helper IV

Calculate 5 day average of daily max values

I have table of data with 2 columns:

1. Date 

2. Value (decimal value)

 

Table has multiple values per day eg more than one row with same date.

 

I am trying to create a DAX Measure that calculates a rolling 5 day average of daily max values.

 

This formula steps is illustrated in the table below.

 

Steps:

1. filter data to most recent 5 days of data

2. calculate daily max for each of the 5 days

3. calculate the average of those 5 days' daily max values

 

 

Date Valuedaily max5 day avg of daily max
2022-05-01 10  
2022-05-01 610 
2022-05-02 200  
2022-05-02 23200 
2022-05-03 12 105.0
2022-05-03 222277.3
2022-05-04 3 111.0
2022-05-04 353585.7
2022-05-05 43 28.5
2022-05-05 114333.3
2022-05-06 77 39.0
2022-05-06 13113169.7

 

I can get average of all of my tables daily maximums:

AVERAGEX(MAXX(ALL('Table'[Date]), 'Table'[Value]))

 

I can get average of past 5 days (note I am using TOP5 as it is possible that the past five days might not be contiguous eg they may be missing days):

 

Last 5 Days Avg =
VAR maxDate = MAX('Table'[Date])
VAR last5days = TOPN(5, FILTER(ALL('Table'[Date]), 'Table'[Date] <= maxDate), 'Table'[Date], DESC)
RETURN
CALCULATE(AVERAGE('Table'[Value]), last5days
)

 

Now I am trying to put this all together which is not working:

 

Last 5 Days Avg Max Value  =
VAR maxDate = MAX('Table'[Date])
VAR last5days = TOPN(5, FILTER(ALL('Table'[Date]), 'Table'[Date] <= maxDate), 'Table'[Date], DESC)
RETURN
CALCULATE (
    AVERAGEX(MAX('Table'[Value])),
    last5days
)
 
What is next ? : )

 

 

 

1 ACCEPTED SOLUTION

@009co 

Please try

Last 5 Days Avg Max Value =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR DatesBeforeTable =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MaxDate )
VAR Last5DaysTable =
    TOPN ( 5, DatesBeforeTable, 'Table'[Date] )
VAR MinDate =
    MINX ( Last5DaysTable, 'Table'[Date] )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( 'Date'[Date] ), CALCULATE ( MAX ( 'Table'[Value] ) ) ),
        'Date'[Date] <= MaxDate,
        'Date'[Date] >= MinDate
    )

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

Hi @009co 

please first make sure to have a date table. This will make everything easier. Create the 1 to many relationship then try this measure 

 

Last 5 Days Avg Max Value =
CALCULATE (
    AVERAGEX ( VALUES ( 'Date'[Date] ), CALCULATE ( MAX ( 'Table'[Value] ) ) ),
    'Date'[Date] <= MAX ( 'Date'[Date] ),
    'Date'[Date] > MAX ( 'Date'[Date] ) - 5
)

 

Thanks, this works perfectly with the calendar table, which I had already. 

 

However, some dates may not have values. Therefore the calendar date may calculate the average using fewer than 5 days' data.

 

Hence reason I was trying to use TOPN of the 'Table'[Date] <= maxDate to get the top days with data.

 

 

@009co 

Please try

Last 5 Days Avg Max Value =
VAR MaxDate =
    MAX ( 'Date'[Date] )
VAR DatesBeforeTable =
    FILTER ( ALL ( 'Table' ), 'Table'[Date] <= MaxDate )
VAR Last5DaysTable =
    TOPN ( 5, DatesBeforeTable, 'Table'[Date] )
VAR MinDate =
    MINX ( Last5DaysTable, 'Table'[Date] )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( 'Date'[Date] ), CALCULATE ( MAX ( 'Table'[Value] ) ) ),
        'Date'[Date] <= MaxDate,
        'Date'[Date] >= MinDate
    )

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