topic Re: Average of Top 5 days over the past 365 days (1 year) in DAX Commands and Tips

Average of Top 5 days over the past 365 days (1 year)

Anonymous — Tue, 30 Aug 2022 16:55:55 GMT

Hello,

I'm stuck trying to calculate the average of my 5 highest production days over the past 365 days, or technically it would be 365 days from the last production entry date.

Currently, I believe I'm able to calculate the rolling average of all production over the last 365 days, but I'm stuck identifying the top 5 days and calculating the average of those days.

I have the below measure so far. As a FYI, [TPAOH] is the metric I'm trying to capture the average of the top 5 on. There are only two tables I'm using, which are daily production and a dates table.

PROD 5=

VAR NumOfMonths = 12

VAR LastCurrentDate=

MAX(Dates[Date])

VAR Periodex =

DATESINPERIOD(Dates[Date], LastCurrentDate, -12, MONTH)

VAR Result =

CALCULATE(

AVERAGEX(

VALUES('Dates'[Date]), [TPAOH]), Periodex)

VAR FirstDateInPeriod = MINX(Periodex, 'Dates'[Date])

VAR LastDatewithProd = MAX('Daily Production'[Date])

RETURN

IF(FirstDateInPeriod <= LastDatewithProd, Result)

Re: Average of Top 5 days over the past 365 days (1 year)

amitchandak — Tue, 30 Aug 2022 17:27:19 GMT

Anonymous , try measures like

M1 = sum(Table[Values])

Top 5 =

CALCULATE( AverageX(Values('Date'[Date]), [M1]) ,TOPN(5,allselected('Date'[Date]), [M1] ,DESC),values('Table'[Date]),
DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH) )

Top 5 =

CALCULATE(
CALCULATE( AverageX(Values('Date'[Date]), [M1]) ,TOPN(5,allselected('Date'[Date]), [M1] ,DESC),values('Table'[Date])),
DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH) )

Re: Average of Top 5 days over the past 365 days (1 year)

Anonymous — Tue, 30 Aug 2022 21:40:58 GMT

This is giving me a top 5 average which is great, but it still seems to only calculate the average of the top 5 based on the selection criteria for date. Ex. it will show the top 5 average based off two weeks if I filter for two weeks. If I show all dates in a table it will just show me the same total of that day since it's just dividing it by one.

Is there a way to show the top 5 average of the past 365 days based off a selected date?

Thanks!