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

Need Help

fhill
8 years ago
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...

fhill

Resident Rockstar

8 years ago

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

Helper I

8 years ago

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

CreatedDate	EffectiveClosedDate__c	CaseNumber	AutoTeam__c	ProblemNumber__c	RecordTypeId
8/31/2017 0:00	8/31/2017 0:00	10-000	AMER-SH		1449000xWW
8/2/2017 0:00	8/3/2017 0:00	10-000	AMER-SSM-Core		1449000xWW
8/8/2017 0:00	8/8/2017 0:00	10-000	AMER-SSM-Core		1449000xWW
9/14/2017 0:00	10/4/2017 0:00	10-000	AMER-SSM-Dist		1449000xWW
8/14/2017 0:00	8/14/2017 0:00	10-000	CSO-MGR		1449000xWW
8/18/2017 0:00	8/18/2017 0:00	10-000	EMEA-AS		1449000xWW
9/20/2017 0:00	10/4/2017 0:00	10-000	EMEA-AS		1449000xWW
9/27/2017 0:00	9/27/2017 0:00	10-000	EMEA-SD		1449000xWW
10/3/2017 0:00	10/4/2017 0:00	10-000	EMEA-SSM-PL		1449000xWW
10/9/2017 0:00		10-000	EMEA-SSM-PL		1449000xWW
8/14/2017 0:00	8/14/2017 0:00	10-001	AMER-SD	22x	1449000xWW
9/20/2017 0:00	9/25/2017 0:00	10-001	AMER-SH		xx221
9/8/2017 0:00	9/8/2017 0:00	10-001	AMER-SSM-Asset		1449000xWW
8/2/2017 0:00	8/23/2017 0:00	10-001	AMER-SSM-Core		1449000xWW
8/18/2017 0:00	9/1/2017 0:00	10-001	AMER-SSM-Core		1449000xWW
9/14/2017 0:00	9/27/2017 0:00	10-001	AMER-SSM-Dist		1449000xWW
10/3/2017 0:00		10-001	AMER-SSM-Sec		1449000xWW
8/24/2017 0:00	8/29/2017 0:00	10-001	AMER-WL		1449000xWW
10/9/2017 0:00	10/9/2017 0:00	10-001	AMER-WL		1449000xWW
8/31/2017 0:00	9/7/2017 0:00	10-001	CC		1449000xWW
7/27/2017 0:00	8/4/2017 0:00	10-001	EMEA-SD		1449000xWW
9/27/2017 0:00	9/27/2017 0:00	10-001	EMEA-SD		1449000xWW
7/5/2017 0:00	8/8/2017 0:00	10-001	EMEA-SSM-PL		1449000xWW
8/14/2017 0:00	8/21/2017 0:00	10-002	AMER-SSM-Asset		1449000xWW
8/2/2017 0:00	8/2/2017 0:00	10-002	AMER-SSM-Core	22x	1449000xWW
8/18/2017 0:00	8/21/2017 0:00	10-002	AMER-SSM-Dist		1449000xWW
9/20/2017 0:00	9/20/2017 0:00	10-002	AMER-SSM-Sec		1449000xWW
9/14/2017 0:00	9/15/2017 0:00	10-002	EMEA-AS		1449000xWW
8/31/2017 0:00		10-002	EMEA-AS		1449000xWW
8/8/2017 0:00	9/6/2017 0:00	10-002	EMEA-SSM-PL		1449000xWW
10/3/2017 0:00		10-002	EMEA-SSM-PL		1449000xWW
10/9/2017 0:00		10-002	EMEA-SSM-PL		1449000xWW
10/9/2017 0:00	10/11/2017 0:00	10-003	AMER-SD		1449000xWW
8/24/2017 0:00	8/24/2017 0:00	10-003	AMER-SH		1449000xWW
9/14/2017 0:00	9/18/2017 0:00	10-003	AMER-SSM-Asset		xx221
8/18/2017 0:00	10/6/2017 0:00	10-003	AMER-SSM-Core		1449000xWW
9/8/2017 0:00	9/11/2017 0:00	10-003	AMER-SSM-Dist		1449000xWW
9/20/2017 0:00	9/29/2017 0:00	10-003	AMER-SSM-Sec		1449000xWW
8/31/2017 0:00	9/1/2017 0:00	10-003	CC		1449000xWW
8/14/2017 0:00	8/14/2017 0:00	10-003	CSO-MGR		1449000xWW
7/21/2017 0:00	8/18/2017 0:00	10-003	EMEA-AS		1449000xWW
7/27/2017 0:00	8/29/2017 0:00	10-003	EMEA-AS		1449000xWW
8/2/2017 0:00	8/28/2017 0:00	10-003	EMEA-SSM-PL		1449000xWW
10/3/2017 0:00	10/9/2017 0:00	10-003	EMEA-SSM-PL		1449000xWW
8/2/2017 0:00	8/11/2017 0:00	10-004	AMER-SH		1449000xWW
8/18/2017 0:00	8/18/2017 0:00	10-004	AMER-SH		1449000xWW
10/9/2017 0:00		10-004	AMER-SH		1449000xWW
8/8/2017 0:00	8/10/2017 0:00	10-004	AMER-SSM-Asset		1449000xWW
8/31/2017 0:00	8/31/2017 0:00	10-004	AMER-SSM-Core		1449000xWW
9/14/2017 0:00	9/15/2017 0:00	10-004	AMER-SSM-Dist		1449000xWW

Forum Discussion

Case Backlog Age

Related content

Backlog average age by day

Age Bins

Case age calculation

stock ageing

Calculating case age