March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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 | Value | daily max | 5 day avg of daily max | |
2022-05-01 | 10 | |||
2022-05-01 | 6 | 10 | ||
2022-05-02 | 200 | |||
2022-05-02 | 23 | 200 | ||
2022-05-03 | 12 | 105.0 | ||
2022-05-03 | 22 | 22 | 77.3 | |
2022-05-04 | 3 | 111.0 | ||
2022-05-04 | 35 | 35 | 85.7 | |
2022-05-05 | 43 | 28.5 | ||
2022-05-05 | 11 | 43 | 33.3 | |
2022-05-06 | 77 | 39.0 | ||
2022-05-06 | 131 | 131 | 69.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:
Solved! Go to Solution.
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
)
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.
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
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
23 | |
15 | |
12 | |
9 | |
8 |
User | Count |
---|---|
41 | |
32 | |
29 | |
12 | |
12 |