We are currently using this formula to get the Overdue issues based on the due date:
[Measures].[Issues with due date]>0 and
isempty([Measures].[Issues not resolved]) and
DateCompare([Measures].[Issue due date],Now())<0
Now we want to get the average time/days the issues are overdue based on the formula above from their due date up to the present. Can you please help us with the formula?
I can’t test this on my side since your formula uses custom measures, but here is what I think would work for you.
First, create a Total Overdue Days measure like this:
[Measures].[Overdue Issue (your calculated measure here)] > 0
-- total time from issue due date till now
Then, you could create your average
CASE WHEN [Measures].[Overdue Issue (your calculated measure here)] > 0 THEN
[Measures].[Total Overdue Days] / [Measures].[Overdue Issue (your calculated measure here)]
Let me know how this works for you!
Thank you for your response.
I created the formula based on yours, however, I could not get the data out of it. See results below.