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

Hmmm... Replacing ALL with ALLSELECTED does not seem to alter the data any.  It seems that any filters from the Case table that I add to the chart are still being ignored.  Is there some sort of relationship I need to establish between these tables for it to work properly?

 

Sorry for all the questions, I am so close here I just have been looking at this for days now :)

 

 

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.

  • Rmilczarek's avatar
    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

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

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

    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?

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