Forum Discussion
arp2
Helper II
Helper IIHelp with slicer measure
Team, I have three tables showing weekly forecast, capacity, and plan by discipline, Project, and Grouping. I've created measures for the Average of the Top 5 weeks (for Forecast) and Max of Capacity...
arp2Helper II
Helper III want the AvgTop5Weeks measure to calculate the average of top 5 weeks which are after a specified date. Currently it is calculating the average of top 5 weeks among all weeks listed in the table. Example, let's say I want the average of Top 5 weeks which are after week 28th March.
Kaviraj11
Solution Sage
Solution Sagehere is the DAX below:
you can use DateAdd function as well, to verify the result, you can create a calcualted column anf using the summarize part of the function to validate your result
AvgTop5Weeks =
AVERAGEX(TOPN(5,FILTER(SUMMARIZE(ALLSELECTED(ForecastTable),ForecastTable[Week],"TotWeek",[SumFcstWeek]
),ForecastTable[Week]<=TODAY()),[TotWeek],DESC),[TotWeek])
- arp2
In above, where and how do I specify the date after which I want the measure to calculate the average?
- Kaviraj11
ForecastTable[Week]<=TODAY())
above expression will consider all records that are less than equal to today
If you want to do it by DAY,Month,YEAR, you can use
ForecastTable[Week]>=DATESINPERIOD(ForecastTable[Week], TODAY(), 11, DAY)