Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
@Alex_LevelUp , 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) )
or
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) )
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!
User | Count |
---|---|
50 | |
24 | |
19 | |
18 | |
14 |
User | Count |
---|---|
91 | |
86 | |
39 | |
25 | |
22 |