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

Re: Case Backlog Age

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

Rmilczarek Regular Visitor
Regular Visitor

Re: Case Backlog Age

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

Rmilczarek Regular Visitor
Regular Visitor

Re: Case Backlog Age

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?

Rmilczarek Regular Visitor
Regular Visitor

Re: Case Backlog Age

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?

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors