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...
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
RemovedColumn
I 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(...
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 :)
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?