Forum Discussion
dogt1225
5 years agoHelper III
Rolling Average Calc
I need some assistance with my Rolling Average calculation. Looking to get the Rolling average of my ADR (Average Days to Resolve) measure.
ADR =
IF(
[Cumulative Sum of Days to Resolve (CALC for ADR)] / [Date Resolved (Count)] = BLANK(),
0,
[Cumulative Sum of Days to Resolve (CALC for ADR)] / [Date Resolved (Count)]
)
Cumulative Sum of Days to Resolve (CALC for ADR) =
CALCULATE(
SUMX(LFR, DATEDIFF(LFR[Submitted_Date], LFR[Date Resolved], DAY)),
USERELATIONSHIP(LFR[Date Resolved], 'Calendar'[Date])
)
Date Resolved (Count) =
CALCULATE(
COUNT(LFR[Date Resolved]),
USERELATIONSHIP(LFR[Date Resolved], 'Calendar'[Date]),
LFR[Date Resolved] <> BLANK(),
LFR[Submission Type] <> BLANK()
)
The Rolling Average would restart every Fiscal Year. Our Fiscal Year starts on the 5th Saturday of the Calendar year, so FY22 starts on 1/30/2021.
Heres what I have so far :
ADR Rolling v4 = CALCULATE(
AVERAGEX('Calendar', [ADR]),
DATESINPERIOD('Calendar'[Date], MIN(LFR[Date Resolved]), 7, DAY))
Found a resolution! Thanks to this youtube video:
https://www.youtube.com/watch?v=nQS6ZO45JNA
3 Replies
- lbendlinSuper User
You are comparing the result of a division with blank? Please use DIVIDE() instead, it has error handling built in.
- dogt1225Helper III
Found a resolution! Thanks to this youtube video:
https://www.youtube.com/watch?v=nQS6ZO45JNA