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

     

fhill's avatar
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's avatar
Rmilczarek
Helper I
8 years ago

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

 

 

Again, I'll get some sample data together and thanks for all of your help!

  • Rmilczarek's avatar
    Rmilczarek
    Helper I
    8 years ago

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

  • scotton's avatar
    scotton
    Frequent Visitor
    4 years ago

    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.