Forum Discussion
Post PartisanAverages only for latest
Dear all,
I need to calculate the average amount days a case has been open. I have the Open and Resolved date and I added the number of days with a calculated column (I'm not sure if this was necessary). So far so good.
When I calculate the average from that column with:
AvrgCaseDaysOpen = AVERAGE(CaseResolution[DaysOpen])
the total value that I'm obtaining is not correct. The problem is that the Case ID are not unique, since a case can be resolved more that once.
I need the average if the CaseID repeats, only for the latest "ResolvedOn"
I tried:
Measure = CALCULATE([AvrgCaseDaysOpen];
FILTER(CaseResolution;MAX(CaseResolution[ResolvedOn])))but the result is the same.
Could somebody give me a hand with this, please?
Maybe try making a conditional column that returns the latest resolved date for that particular case number across all instances of that case number in the table (forget the whole syntax you'd probably need to wrap the max statement you had in an allexcept on your case column), then compare it to the resolved date in each particular row - that'll flag those which aren't the latest on that case, which you can then filter on to make your calculations right?
1 Reply
jthomsonSolution Sage
Maybe try making a conditional column that returns the latest resolved date for that particular case number across all instances of that case number in the table (forget the whole syntax you'd probably need to wrap the max statement you had in an allexcept on your case column), then compare it to the resolved date in each particular row - that'll flag those which aren't the latest on that case, which you can then filter on to make your calculations right?