cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rmilczarek Regular Visitor
Regular Visitor

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:

2017-10-17_11-05-07.png

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
fhill Senior Member
Senior Member

Re: Case Backlog Age

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

Capture.PNG

 

View solution in original post

14 REPLIES 14
fhill Senior Member
Senior Member

Re: Case Backlog Age

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

 

 

 

Capture.PNG

 

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.

Capture2.PNG

Rmilczarek Regular Visitor
Regular Visitor

Re: Case Backlog Age

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?

fhill Senior Member
Senior Member

Re: Case Backlog Age

 

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

 

fhill Senior Member
Senior Member

Re: Case Backlog Age

I found this with a quick Google...  In Query Editor -> New Source -> Blank Query then select 'Advanced Editor' from the ribbon and paste this formula.  The -100..100 is the number of months backwards and forwards to calculate from Date.Time.LocalNow(), so modify as needed.

 

 

let
Source = Table.FromList({-100..100}, each{_}),
AddedStartOfMonth = Table.AddColumn(Source, "StartOfMonth", each Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),[Column1])), type date),
AddedEndOfMonth = Table.AddColumn(AddedStartOfMonth, "EndOfMonth", each Date.EndOfMonth([StartOfMonth]), type date),
RemovedColumn = Table.RemoveColumns(AddedEndOfMonth,{"Column1"})
in
RemovedColumn

Rmilczarek Regular Visitor
Regular Visitor

Re: Case Backlog Age

I have made great strides on this (I think) thanks to your help.  I am facing a bit of a data bloat dilemma now though as the numbers I am getting on my chart are much higher than they should be.  I am pretty sure it is because when I use the data from the Months table that I created, it is using the entire dataset but in my other charts in this report I am using data from the Case table which is filtered to show only cases from certain teams.  

 

Can you suggest a way I can apply these Case table filters to the Months table I created?  I've tried a few ways via relationships (causes the chart to crash) or via column adds (either cannot get the data I want from the other table or it shows no data).

  

Here is what the chart looks like now so I am definately on the right track!

2017-10-18_13-31-28.png

fhill Senior Member
Senior Member

Re: Case Backlog Age

Change every ... FILTER ( ALL( ... to   FILTER(ALLSELECTED(...

Rmilczarek Regular Visitor
Regular Visitor

Re: Case Backlog Age

Hmmm... Replacing ALL with ALLSELECTED does not seem to alter the data any.  It seems that any filters from the Case table that I add to the chart are still being ignored.  Is there some sort of relationship I need to establish between these tables for it to work properly?

 

Sorry for all the questions, I am so close here I just have been looking at this for days now 🙂

 

 

fhill Senior Member
Senior Member

Re: Case Backlog Age

Try ALLEXCEPT. If that doesnt work, can you post a sample of yourdata with the additional filtered rows so i can duplicate on my end?

Rmilczarek Regular Visitor
Regular Visitor

Re: Case Backlog Age

Sadly ALLEXCEPT didn't seem to budge the data either.  I'll get a sample of the data together to attach but in the meantime, here is the method I am using to determine the Backlog for the months in question.  I have verified this matches the actual backlog, so I know this method is accurate.  Is there any way to add this type of formula/setup to "define" the parameters of a Backlog Age by Month report?  

 

Here is what I used for Backlog:

 

Backlog - Monthly = CALCULATE('Case'[Backlog],FILTER(ALL('Calendar'),'Calendar'[Date]<=MIN('Calendar'[Date])))

 

Shared Axis is defined on the Calendar table I made as  Monthly = FORMAT('Calendar'[Date],"YYYY/mm")

 

2017-10-19_7-35-58.png

 

Again, I'll get some sample data together and thanks for all of your help!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 89 members 1,427 guests
Please welcome our newest community members: