Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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 |
---|---|
57 | |
46 | |
19 | |
16 | |
15 |
User | Count |
---|---|
116 | |
41 | |
40 | |
28 | |
22 |