Forum Discussion
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...
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...
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?