Forum Discussion
Case Backlog Age
I had a previous post wherein I was looking for the average of cases open per month for a defined time period and the calculations seem to work well. However, now I have further analysis I am trying to perform for the following with Case Backlog being defined as cases that were open each month for a defined period (in this case, the past 15 months).
- How many cases in the Backlog had been open for:
- Less than 60 days
- 60 - 120 days
- 120 - 180 days
- More than 180 days
- What was the Average Open Age for cases in the Backlog for each month in the period
My data table consists of:
- Open Date
- Closed Date
- Case Number
The resulting chart when done in Excel for another project with monthly "snapshots" looks like this:
The problem with trying to get a similar report in Power BI is that I do not have the monthly snapshots for the data set I am working with (the above was for a different project), all I have is the entire case database.
As mentioned, I have the total number of cases in the backlog that make up these monthly numbers, but now I need them broken down by the ages at the time of calculation for each month. Keep in mind, nearly all of the cases in Jan 16 are now closed, but in Jan 16, they were open and the chart shows how old those Open cases were at that time.
Thanks in advance!
Ryan
Ok, I made some small changes to my code to account for Cases opening on Month End, but in general the AllSelected worked... Remember to create a new Table with Month Start and Month End, and use this 'Months' table to create new Columns to calculate your timeframe values.
** New Column on your Case Table **
Adj_CloseDate = IF (ISBLANK(Table1[EffectiveClosedDate__c]), TODAY(),IF(Table1[EffectiveClosedDate__c] < Table1[CreatedDate],Table1[CreatedDate],Table1[EffectiveClosedDate__c]))
** All these columns go on the Months (open & Close) table.
<60 = CALCULATE(COUNT(Table1[CaseNumber]), FILTER(ALLSELECTED(Table1),
Table1[CreatedDate] <= tbl_Months[EndOfMonth] && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth],Table1[Adj_CloseDate]),DAY) < 60 && Table1[Adj_CloseDate] >= tbl_Months[EndOfMonth]))60-120 = CALCULATE(COUNT(Table1[CaseNumber]), FILTER(ALLSELECTED(Table1),
Table1[CreatedDate] <= tbl_Months[EndOfMonth] && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth],Table1[Adj_CloseDate]),DAY) >= 60 && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth] , Table1[Adj_CloseDate]),DAY) < 120 && Table1[Adj_CloseDate] >= tbl_Months[EndOfMonth]))120-180 = CALCULATE(COUNT(Table1[CaseNumber]), FILTER(ALLSELECTED(Table1),
Table1[CreatedDate] <= tbl_Months[EndOfMonth] && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth],Table1[Adj_CloseDate]),DAY) >= 120 && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth] , Table1[Adj_CloseDate]),DAY) < 180 && Table1[Adj_CloseDate] >= tbl_Months[EndOfMonth]))>180 = CALCULATE(COUNT(Table1[CaseNumber]), FILTER(ALL(Table1),
Table1[CreatedDate] <= tbl_Months[EndOfMonth] && DATEDIFF(Table1[CreatedDate], IF(Table1[Adj_CloseDate] > tbl_Months[EndOfMonth], tbl_Months[EndOfMonth],Table1[Adj_CloseDate]),DAY) >= 180 && Table1[Adj_CloseDate] >= tbl_Months[EndOfMonth]))** for the purpsoe of the screen shot, >180 is set to >90 since this is all pretty recent data...
15 Replies
** New Column *Not Measure* on your Case Data Table ** 2nd If is to prevent bad data from breaking my code.
Adj_ClosedDate = IF (ISBLANK(CaseHistory[ClosedDate]), TODAY(), IF( CaseHistory[ClosedDate] < CaseHistory[OpenDate], CaseHistory[OpenDate], CaseHistory[ClosedDate]))
Now you have to build a MONTH table with start and end dates for each month. You can use Excel or anything to get the start of each month, and see my 2nd screen shot where Power BI can automatically calculate the End of Each month with a Transform feature.
** Add these Custom Columns ** again not measures ** to the Month column as your 'running summary'. Then graph as you desire, my sample is below...
<60 = CALCULATE(COUNT(CaseHistory[CaseID]), FILTER(ALL(CaseHistory),
CaseHistory[OpenDate] < Months[EoM] && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) < 60 && CaseHistory[Adj_ClosedDate] > Months[EoM]))60-120 = CALCULATE(COUNT(CaseHistory[CaseID]), FILTER(ALL(CaseHistory),
CaseHistory[OpenDate] < Months[EoM] && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) >= 60 && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) < 120 && CaseHistory[Adj_ClosedDate] > Months[EoM]))120-180 = CALCULATE(COUNT(CaseHistory[CaseID]), FILTER(ALL(CaseHistory),
CaseHistory[OpenDate] < Months[EoM] && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) >= 120 && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) < 180 && CaseHistory[Adj_ClosedDate] > Months[EoM]))>180 = CALCULATE(COUNT(CaseHistory[CaseID]), FILTER(ALL(CaseHistory),
CaseHistory[OpenDate] < Months[EoM] && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) >= 180 && CaseHistory[Adj_ClosedDate] > Months[EoM]))Paste in or import a list of every start od Month, then duplicate the column and use this Transform feature to get EoM. EoM will be used in the calculations, but the Months will be used for graphing to get 'easy to read' month starts.
Thanks for this detailed response! I like where this is going so far :)
For the Month table, how should I start the table creation? Just something like this:
Month = CALENDAR(DATE(2001,01,01),DATE(2020,12,31))
And then add the columns you reference to the table?
That command will create a Daily Calendar table.
I just cheated and entered 1/1/2017 & 2/1/2017 (MM/DD/YYYY - US formatting) in Excel then dragged down a few months to have Excel create 'Month Start' values. I then imported this excel as the start of the Months. (Cheating I know, and not very programatic... but it works and is easy.) If you don't want to keep the Excel fiel laying around, you can just 'Enter Data' and Paste the values from Excel.
FOrrest
