Time to clear a backlog

[JediCB]

My overall intent here is to calculate the time it will take to clear a backlog.

For this example only the current number of open cases is 50000.

I have a Case Start Date and a Case Closed Date and I need to figure out how to show how long it is going to take to clear the backlog.

Table

- Case Start Date

- Case Closed Date

- Case_ID

How can I write a DAX measure/column/table to calculate the time it will take to clear the backlog in weeks?

Also, I want to create a "What If" to say if the backlog was cleared at 500 per week instead, what would the time to clear the backlog be then.

[JediCB]

Thanks for your response.

I can't reveal our business info because it's sensitive, but let's compare it to say an insurance company that has case managers for insurance claims. A claim is submitted (it's now open) by the customer, a case manager picks it up, makes an assessment, sends it to the underwriter, the underwriter sends it back, the case manager then makes a decision, then the case is closed.

Yes, we have a fact table that has case history. This table is huge.

So, simply, an open case is a case where it doesn't have a closed date yet or the closed date is < max(date).

There is no information about FTE in the db so we just want to use a slicer (or a what if parameter) to calculate to possible changes to the backlog on the fly. Say 500 cleared per week, 600 cleared per week, 700 cleared per week etc.

I hope this information makes it easier to help me. Once again, thanks.

[scee07 · Accepted solution]

Hi,

I have now worked on a possible solution based on my current understanding.

Key assumptions:

• The closed cases per day give an indication of future performance on open cases
• All open cases are backlog

I simulated cases randomly. So, if you really look at this, a word of warning is that every time a measure is recalculated all random numbers change. And you see changes in numbers that will not occur in the real case.

Although not relevant for you, I will explain how I did the simulation. Just for the record and if someone else reads this.

ClosedEndDate = DATE ( 2022, 12, 12 )
ClosedStartDate =DATE ( 2022, 1, 1 )
NumberClosedCases = 100000
NumberOpenCases = 50000
OpenEndDate = DATE ( 2023, 2, 2 )
OpenStartDate = DATE ( 2022, 11, 1 )

ClosedCases =
GENERATESERIES ( 1, [NumberClosedCases], 1 )

StartDate =
[ClosedStartDate]
    + RANDBETWEEN ( 0, CONVERT ( [ClosedEndDate] - [ClosedStartDate], INTEGER ) )
CloseDate =
ClosedCases[StartDate] + RANDBETWEEN ( 0, 10 )

OpenCases =
GENERATESERIES (
    1 + [NumberClosedCases],
    1 + [NumberClosedCases] + [NumberOpenCases],
    1
)
StartDate =
[OpenStartDate]
    + RANDBETWEEN ( 0, CONVERT ( [OpenEndDate] - [OpenEndDate], INTEGER ) )
CloseDate =
BLANK ()
Cases =
UNION ( ClosedCases, OpenCases )

Basically case IDs are generated, random start dates in a pre-defined time interval are chosen and for the closed cases processing period randomly between 0 and 10 day is applied.

Then the “Cases” table is the union of the closed and open cases

An intermediate table is created that gives the number of closed cases per day

ClosedCasesPerDay =

SUMMARIZE (

    FILTER ( 'Cases', NOT ISBLANK ( 'Cases'[CloseDate] ) ),

    'Cases'[CloseDate],

    "Count", COUNT ( 'Cases'[CaseID] )

)

The base measure is then the average number of cases close per day.

Working days are set to 5 per week to get the number of weekly closed cases

From the table above we can calculate the standard deviation and the 95% confidence interval for the average. This gives a lower boundary for the number of cases closed per day. 2 times the standard deviation gives the lower boundary for the average.

AvgClosedCasesPerDay =
AVERAGE ( ClosedCasesPerDay[Count] )

StdDevClosedCasesPerDay =
STDEV.S ( ClosedCasesPerDay[Count] )
AverageWorkoffDurationInWeeks =
SELECTEDVALUE ( BacklogCasesFilter[OpenCases] ) / ( [NumberWorkingDaysPerWeek] * [AvgClosedCasesPerDay] )

WorkoffDurationInWeeks95Percent =
SELECTEDVALUE ( BacklogCasesFilter[OpenCases] ) / ( [NumberWorkingDaysPerWeek] * [Confidence95Percent] )

As you can see a filter for the number of open cases is defined. This is done by defining a step size, e.g. 500 and creating all numbers in steps from step to total number of open cases.

BacklogCasesFilter =

GENERATESERIES ( 500, [TotalNumberOpenCases], [FilterStepSize] )

Basically the filter value is taken and the average is applied to get the number of weeks necessary to work off the open cases.

Here is the link to the file.

https://scheerenconsult-my.sharepoint.com/:u:/g/personal/christian_scheerenconsult_onmicrosoft_com/E...

This is how I would do it in the simplest form. Let me know if this suits your needs. Happy to bring this to a mutual agreement.

Best regards

Christian

[JediCB]

Thanks very much for the detailed response. It looks great so far. However there's 1 change that I can't figure out how to adjust.

The BackLogCases filter needs to be cases cleared per week, not number of current open cases. So, for example the current number of cases still open are 50000. The user then clicks a filter that has:
500 cases per week (current BAU)
600 cases per week (an increase by 100 cases per week)
700 cases per week (an increase by 200 cases per week)
etc...

and the result will show how many weeks it will take to clear that backlog given the user's selection.

[JediCB]

There's one other thing that needs to be calculated I think. You can tell me if I'm wrong though.

Does there need to be some sort of calculation to take into consideration the average number of new open cases per day/week?

[scee07]

Hi, 
goes back to the inital question, to define the business scenario.

There are 2 types of questions one could ask:

a) under current capacities, how long does it take to work off x cases (version 1.0). Or, how many cases can I work off in a time period t.

b) Somebody tells me they have a cacpacity of n cases per week. How long does it take to work off  x cases (version 2.0)

In b) you work with a given (however derived) capacity. So your actual capacity is not relevant for the question. 

So depends on the information you have and what you want. 

Let me know if we can further refine the business scenario. 

Best regards 

Christian

[scee07]

Hi,
if I understand this correctly, you say you select a weekly capacity that you have to work off the backlog and  then you just calculate the weeks to work off the entire backlog which is visible at the moment. If this is true, the measure gets simpler:

AverageWorkoffDurationInWeeks =
DIVIDE (
    [TotalNumberOpenCases],
    SELECTEDVALUE ( BacklogCasesFilter[Weekly Capacity] )
)

Let me know if this is the desired scenario. Otherwise, happy to adjust it. 
Here is the updated file:
Backlog_2.0.pbix
Best regards 
Christian

[scee07]

Hi, 
I think this is clear now: it is always a question of terminology; for you everything that is open is backlog. I was under the impression that there should be some kind of SLA and backlog is out of SLA. Then it is clear that you want to estimate the time to work off stuff under current capacities. 
I will find during the day and come back to you. 
Best regards 
Christian

[scee07]

Hi, 

first glance I'd say insufficient information. But, let me do some assumptions and see where we get.

a) perhaps the case table is your full case history. Then the backlog cases would be open (closed date is open). If you have a service level of e.g. 48 hours then this is not true and recent cases are still in service level. For the moment, let's assume all open cases are in backlog.

b) given a), what does the table tell you about your backlog capacity? The answer is next to nothing. The table tells you that you have a certain number of backlog cases.

c) What the table does tell you: it tells you your historic capacity by date. For the sake of argument, do a helper table  helperTable =  summarize(filter(table, closed date not blank),  [closed date], "Count", Count(CaseID)). This gives you the number of cases closed per date 

d) do  a measure: DailyAvg = Average(helperTable[Count]). This gives you the history case capacity per day.

e) Now ( a lot of assumptions: let's say your business is a call center BPO with 10 FTE working per day on average). Then this give  casesPerFTEPerDay = CaseCapacityPerDay/10FTE

f) Now you say, you can provide 5 additional FTE per day to work on back log. You need more FTE, because your existing capacity/staff is building up back log, right? Then you manage to do 5 x casesPerFTEPerDay in addition ot the daily business. 

g) Then you can calculate the time in working day needed to work off the backlog

So, this is all speculation. Perhaps your table is something else, perhaps you capacity is machines not FTE. 

In summary, the questions must be what is my number of cases of backlog and what additional capacities can I provide to work this off. Then you can calculate the number of days/weeks it takes.

So, let me know your business scenario and the described necessary inputs, then we can work out a solution. 

Furthermore, what I described si the most basic type of forcasting saying the history is the future. This can be refined endlessely (case category comes to mind, statistical forecasting). 

Best regards
Christian