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...
** 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.
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?
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
I found this with a quick Google... In Query Editor -> New Source -> Blank Query then select 'Advanced Editor' from the ribbon and paste this formula. The -100..100 is the number of months backwards and forwards to calculate from Date.Time.LocalNow(), so modify as needed.
let
Source = Table.FromList({-100..100}, each{_}),
AddedStartOfMonth = Table.AddColumn(Source, "StartOfMonth", each Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),[Column1])), type date),
AddedEndOfMonth = Table.AddColumn(AddedStartOfMonth, "EndOfMonth", each Date.EndOfMonth([StartOfMonth]), type date),
RemovedColumn = Table.RemoveColumns(AddedEndOfMonth,{"Column1"})
in
RemovedColumnI have made great strides on this (I think) thanks to your help. I am facing a bit of a data bloat dilemma now though as the numbers I am getting on my chart are much higher than they should be. I am pretty sure it is because when I use the data from the Months table that I created, it is using the entire dataset but in my other charts in this report I am using data from the Case table which is filtered to show only cases from certain teams.
Can you suggest a way I can apply these Case table filters to the Months table I created? I've tried a few ways via relationships (causes the chart to crash) or via column adds (either cannot get the data I want from the other table or it shows no data).
Here is what the chart looks like now so I am definately on the right track!
Change every ... FILTER ( ALL( ... to FILTER(ALLSELECTED(...