Forum Discussion
Helper IIIWeek Ending Average Age of Open Requests
dogt1225 OK, if you have a Week Ending, then you should be able to use the built-in Running Total Quick Measure potentially. Otherwise, potentially:
Cumulative Measure = VAR __Current = MAX([Week Ending]) RETURN SUMX(FILTER(ALL('Table'),[Week Ending]<__Current),[Measure])[Measure] should be whatever you are doing now to calculate it non-cumulatively. Mileage may vary. You may need an ADDCOLUMNS wrapping your FILTER or even a SUMMARIZE. But I think if you use this for your measure:
DATEDIFF(MAX(DataTable[Submitted_Date]), TODAY(), DAY)If you can post your sample data with the Week Ending you calculated I can try to mock it up and test it and be specific.
Helper IIIamitchandak and Greg_Deckler Thank you for the responses.
I was able to create a Week Ending Date for my Calendar table, but I am still unsure how I will use that date to find the cumulative average for a given week.
amitchandak Your link seems to go over comparing this week vs last week. Not quite what I am looking for. The math should be the Cumulative Sum of (Date Difference (Week End Selected - Submitted Date)) / Total Open requests for the week. Any ideas?
Week End Date = CALCULATE(
MIN([Date]),
FILTER('Calendar', WEEKDAY([Date],2)=5 && 'Calendar'[Date]>=EARLIER('Calendar'[Date])
)
)
Community Championdogt1225 OK, if you have a Week Ending, then you should be able to use the built-in Running Total Quick Measure potentially. Otherwise, potentially:
Cumulative Measure =
VAR __Current = MAX([Week Ending])
RETURN
SUMX(FILTER(ALL('Table'),[Week Ending]<__Current),[Measure])[Measure] should be whatever you are doing now to calculate it non-cumulatively. Mileage may vary. You may need an ADDCOLUMNS wrapping your FILTER or even a SUMMARIZE. But I think if you use this for your measure:
DATEDIFF(MAX(DataTable[Submitted_Date]), TODAY(), DAY)If you can post your sample data with the Week Ending you calculated I can try to mock it up and test it and be specific.