Forum Discussion
Rolling Interval Target Needed to Achieve Goal
Hoping someone with a mind for math and DAX can help!
Scenario:
A call center has a service level goal of answering 80% of calls offered within 60 seconds each half hour interval.
By the end of the 9:00 AM interval, only 369/570 total calls offered have been answered at service level goal (64.7%).
There are 1,854 remaining calls forecast for the remaining intervals for the day.
The 570 calls offered plus the 1,854 remaining calls forecast equals an estimated total of 2,424 calls for the day.
Problem:
Given the ratio of calls answered at goal by the end of the 9:30 AM interval (64.7%), how many calls during each of the remaining intervals for the day need to be answered at goal?
My approach so far:
Measure = (((TotalOffered + Forecast Remaining)*.8) - TotalAnswered) / Forecast Remaining
Problem-solving reasoning:
2424 max possible calls offered
2424 x .80 = 1939 total calls answered in service level needed
1854 forecast calls remain
369 calls answered in service level
1939 total calls answered in service level needed - 369 calls answered in service level = 1570
1570 / 1854 forecasted calls remaining = 84.7% of calls answered in service level needed for rest of day
Where I'm stuck:
The measure is including the intervals that have passed already and I don't think it should since the remaining intervals are the only chance to move the average towards the goal of 1939/2424 total calls answered.
Hi daxBeanz ,
Please try:
Measure = VAR _TotalOffered = SUMX ( FILTER ( ALL ( Table1 ), [Interval] < MAX ( 'Table1'[Interval] ) ), [Sum of calls offered] ) VAR _ForecastRemaining = SUMX ( FILTER ( ALL ( Table1 ), [Interval] >= MAX ( 'Table1'[Interval] ) ), [Sum of Forecast Calls] ) VAR _TotalAnswered = SUMX ( FILTER ( ALL ( Table1 ), [Interval] < MAX ( 'Table1'[Interval] ) ), [Sum of Calls Answered at Goal] ) VAR _a = ( _TotalOffered + _ForecastRemaining ) * 0.8 - _TotalAnswered RETURN IF ( ISBLANK ( MAX ( 'Table1'[Sum of Forecast Calls] ) ), BLANK (), DIVIDE ( _a, _ForecastRemaining ) )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
- v-jianboli-msft
Community Support
Hi daxBeanz ,
Please try:
Measure = VAR _TotalOffered = SUMX ( FILTER ( ALL ( Table1 ), [Interval] < MAX ( 'Table1'[Interval] ) ), [Sum of calls offered] ) VAR _ForecastRemaining = SUMX ( FILTER ( ALL ( Table1 ), [Interval] >= MAX ( 'Table1'[Interval] ) ), [Sum of Forecast Calls] ) VAR _TotalAnswered = SUMX ( FILTER ( ALL ( Table1 ), [Interval] < MAX ( 'Table1'[Interval] ) ), [Sum of Calls Answered at Goal] ) VAR _a = ( _TotalOffered + _ForecastRemaining ) * 0.8 - _TotalAnswered RETURN IF ( ISBLANK ( MAX ( 'Table1'[Sum of Forecast Calls] ) ), BLANK (), DIVIDE ( _a, _ForecastRemaining ) )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.