I need a little bit of help with calculating the average age of open issues over time using DAX.
Using the sample data set:
I want the following results:
I found this article that helps, but I'm missing how to calculate what the age was based on the date.
I think I have this sorted.
Average Age = AVERAGEX( VALUES ('Calendar'[Date]), VAR CurrentDate = 'Calendar'[Date] VAR CreatedBeforeCurrentDate = FILTER ( ALL ( 'Cases'[Start] ), 'Cases'[Start] <= CurrentDate ) VAR ResolvedAfterCurrentDate = FILTER ( ALL ( 'Cases'[End] ), 'Cases'[End] >= CurrentDate || 'Cases'[End] = BLANK() ) RETURN CALCULATE ( AVERAGEX( 'CASES', CurrentDate - 'Cases'[Start] ), CreatedBeforeCurrentDate, ResolvedAfterCurrentDate, ALL ( 'Calendar' ) ) )
Originally, I didn't think the AVERAGEX version was working - but I think it is.
If I change the variable CreatedBeforeCurrentDate to be:
'Cases'[Start] < CurrentDate
I get the value I was expecting.
However, I think thats wrong. On May 2nd, it should be 0.67 not the 1.0 that I anticipated as there are three cases open.