Forum Discussion

dogt1225's avatar
dogt1225
Helper III
5 years ago
Solved

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))

 

 

 

3 Replies