Forum Discussion

daxBeanz's avatar
daxBeanz
Regular Visitor
4 years ago
Solved

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

  • 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.