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

** New Column *Not Measure* on your Case Data Table **  2nd If is to prevent bad data from breaking my code.

Adj_ClosedDate = IF (ISBLANK(CaseHistory[ClosedDate]), TODAY(), IF( CaseHistory[ClosedDate] < CaseHistory[OpenDate], CaseHistory[OpenDate], CaseHistory[ClosedDate]))      

 

Now you have to build a MONTH table with start and end dates for each month.  You can use Excel or anything to get the start of each month, and see my 2nd screen shot where Power BI can automatically calculate the End of Each month with a Transform feature.

 

** Add these Custom Columns ** again not measures ** to the Month column as your 'running summary'.  Then graph as you desire, my sample is below...

 

<60 = CALCULATE(COUNT(CaseHistory[CaseID]), FILTER(ALL(CaseHistory),
CaseHistory[OpenDate] < Months[EoM] && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) < 60 && CaseHistory[Adj_ClosedDate] > Months[EoM]))

 

60-120 = CALCULATE(COUNT(CaseHistory[CaseID]), FILTER(ALL(CaseHistory),
CaseHistory[OpenDate] < Months[EoM] && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) >= 60 && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) < 120 && CaseHistory[Adj_ClosedDate] > Months[EoM]))

 

120-180 = CALCULATE(COUNT(CaseHistory[CaseID]), FILTER(ALL(CaseHistory),
CaseHistory[OpenDate] < Months[EoM] && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) >= 120 && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) < 180 && CaseHistory[Adj_ClosedDate] > Months[EoM]))

 

>180 = CALCULATE(COUNT(CaseHistory[CaseID]), FILTER(ALL(CaseHistory),
CaseHistory[OpenDate] < Months[EoM] && DATEDIFF(CaseHistory[OpenDate], IF(CaseHistory[Adj_ClosedDate] > Months[EoM], Months[EoM],CaseHistory[Adj_ClosedDate]),DAY) >= 180 && CaseHistory[Adj_ClosedDate] > Months[EoM]))

 

 

 

 

Paste in or import a list of every start od Month, then duplicate the column and use this Transform feature to get EoM.  EoM will be used in the calculations, but the Months will be used for graphing to get 'easy to read' month starts.