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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.