Forum Discussion

Rmilczarek's avatar
Rmilczarek
Helper I
8 years ago
Solved

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's avatar
    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...

     

Rmilczarek's avatar
Rmilczarek
Helper I
8 years ago

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's avatar
fhill
Resident Rockstar
8 years ago

 

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