Calculate 5 day average of daily max values

[009co]

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:

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 ? : )

[tamerj1]

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
)

[009co]

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.

[tamerj1 · 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
    )