Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rmilczarek
Helper I
Helper I

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

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

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




View solution in original post

15 REPLIES 15
fhill
Resident Rockstar
Resident Rockstar

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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?

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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

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




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




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 🙂

 

 

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?




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Here is some sample data.  The filters would be ProblemNumber is Blank, RecordTypeID is 1449000xWW and AutoTeam is Not CC

CreatedDateEffectiveClosedDate__cCaseNumberAutoTeam__cProblemNumber__cRecordTypeId
8/31/2017 0:008/31/2017 0:0010-000AMER-SH 1449000xWW
8/2/2017 0:008/3/2017 0:0010-000AMER-SSM-Core 1449000xWW
8/8/2017 0:008/8/2017 0:0010-000AMER-SSM-Core 1449000xWW
9/14/2017 0:0010/4/2017 0:0010-000AMER-SSM-Dist 1449000xWW
8/14/2017 0:008/14/2017 0:0010-000CSO-MGR 1449000xWW
8/18/2017 0:008/18/2017 0:0010-000EMEA-AS 1449000xWW
9/20/2017 0:0010/4/2017 0:0010-000EMEA-AS 1449000xWW
9/27/2017 0:009/27/2017 0:0010-000EMEA-SD 1449000xWW
10/3/2017 0:0010/4/2017 0:0010-000EMEA-SSM-PL 1449000xWW
10/9/2017 0:00 10-000EMEA-SSM-PL 1449000xWW
8/14/2017 0:008/14/2017 0:0010-001AMER-SD22x1449000xWW
9/20/2017 0:009/25/2017 0:0010-001AMER-SH xx221
9/8/2017 0:009/8/2017 0:0010-001AMER-SSM-Asset 1449000xWW
8/2/2017 0:008/23/2017 0:0010-001AMER-SSM-Core 1449000xWW
8/18/2017 0:009/1/2017 0:0010-001AMER-SSM-Core 1449000xWW
9/14/2017 0:009/27/2017 0:0010-001AMER-SSM-Dist 1449000xWW
10/3/2017 0:00 10-001AMER-SSM-Sec 1449000xWW
8/24/2017 0:008/29/2017 0:0010-001AMER-WL 1449000xWW
10/9/2017 0:0010/9/2017 0:0010-001AMER-WL 1449000xWW
8/31/2017 0:009/7/2017 0:0010-001CC 1449000xWW
7/27/2017 0:008/4/2017 0:0010-001EMEA-SD 1449000xWW
9/27/2017 0:009/27/2017 0:0010-001EMEA-SD 1449000xWW
7/5/2017 0:008/8/2017 0:0010-001EMEA-SSM-PL 1449000xWW
8/14/2017 0:008/21/2017 0:0010-002AMER-SSM-Asset 1449000xWW
8/2/2017 0:008/2/2017 0:0010-002AMER-SSM-Core22x1449000xWW
8/18/2017 0:008/21/2017 0:0010-002AMER-SSM-Dist 1449000xWW
9/20/2017 0:009/20/2017 0:0010-002AMER-SSM-Sec 1449000xWW
9/14/2017 0:009/15/2017 0:0010-002EMEA-AS 1449000xWW
8/31/2017 0:00 10-002EMEA-AS 1449000xWW
8/8/2017 0:009/6/2017 0:0010-002EMEA-SSM-PL 1449000xWW
10/3/2017 0:00 10-002EMEA-SSM-PL 1449000xWW
10/9/2017 0:00 10-002EMEA-SSM-PL 1449000xWW
10/9/2017 0:0010/11/2017 0:0010-003AMER-SD 1449000xWW
8/24/2017 0:008/24/2017 0:0010-003AMER-SH 1449000xWW
9/14/2017 0:009/18/2017 0:0010-003AMER-SSM-Asset xx221
8/18/2017 0:0010/6/2017 0:0010-003AMER-SSM-Core 1449000xWW
9/8/2017 0:009/11/2017 0:0010-003AMER-SSM-Dist 1449000xWW
9/20/2017 0:009/29/2017 0:0010-003AMER-SSM-Sec 1449000xWW
8/31/2017 0:009/1/2017 0:0010-003CC 1449000xWW
8/14/2017 0:008/14/2017 0:0010-003CSO-MGR 1449000xWW
7/21/2017 0:008/18/2017 0:0010-003EMEA-AS 1449000xWW
7/27/2017 0:008/29/2017 0:0010-003EMEA-AS 1449000xWW
8/2/2017 0:008/28/2017 0:0010-003EMEA-SSM-PL 1449000xWW
10/3/2017 0:0010/9/2017 0:0010-003EMEA-SSM-PL 1449000xWW
8/2/2017 0:008/11/2017 0:0010-004AMER-SH 1449000xWW
8/18/2017 0:008/18/2017 0:0010-004AMER-SH 1449000xWW
10/9/2017 0:00 10-004AMER-SH 1449000xWW
8/8/2017 0:008/10/2017 0:0010-004AMER-SSM-Asset 1449000xWW
8/31/2017 0:008/31/2017 0:0010-004AMER-SSM-Core 1449000xWW
9/14/2017 0:009/15/2017 0:0010-004AMER-SSM-Dist 1449000xWW

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

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Ugh.  I tried just starting fresh with a new table for Cases and building the Month table.  But now when I put in the columns in the Month table for the ranges, I am getting the "In DATEDIFF function, the start date cannot be greater than the end date" error in every one.

 

These did not error out in my other report so I am not sure what is different now.  Here are my columns (adjusted for column names):

 

>180 = CALCULATE(COUNT('Cases'[CaseNumber]), FILTER(ALL('Cases'),
'Cases'[CreatedDate] < 'Month'[MonthEnd] && DATEDIFF('Cases'[CreatedDate], IF('Cases'[Adj_CloseDate] > 'Month'[MonthEnd], 'Month'[MonthEnd],'Cases'[Adj_CloseDate]),DAY) >= 180 && 'Cases'[Adj_CloseDate] > 'Month'[MonthEnd]))

 

Adj_CloseDate = IF (ISBLANK('Cases'[EffectiveClosedDate__c]), TODAY(),IF('Cases'[EffectiveClosedDate__c] < 'Cases'[CreatedDate],'Cases'[CreatedDate],'Cases'[EffectiveClosedDate__c]))

 

Could something be wrong with the latest data pull?

Sorry, it seems something was wrong with that data pull because I refreshed it and now I don't see errors on those columns.  However, regarding filtering, the chart still seems to be ignoring any filter I add if that filter a field on the Cases table.  Is there something I am supposed to do to get the Month table to recognize fitlers I set on the Cases table?

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!

Hi there. I just transformed those columns (<29...)  to measure and switched
'tbl_Months[EndOfMonth]' to MAX(tbl_Months[EndOfMonth])
Now I can filter freely.

Sorry, I ran into some data issues getting an export.  I will get something uploaded soon.

 

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

 




Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


https://www.linkedin.com/in/forrest-hill-04480730/

Proud to give back to the community!
Thank You!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.