cancel
Showing results for
Did you mean:
Frequent Visitor

Average age of a Case over time

I need a little bit of help with calculating the average age of open issues over time using DAX.

Using the sample data set:

 Case Start End 10001 2018-05-01 2018-05-05 10002 2018-05-03 2018-05-08 10003 2018-05-01 2018-05-09 10004 2018-05-02

I want the following results:

 Date 10001 10002 10003 10004 Average 2018-05-01 0 0 0 0 0 2018-05-02 1 0 1 0 1 2018-05-03 2 0 2 1 1.666667 2018-05-04 3 1 3 2 2.25 2018-05-05 4 2 4 3 3.25 2018-05-06 0 3 5 4 4 2018-05-07 0 4 6 5 5 2018-05-08 0 5 7 6 6 2018-05-09 0 0 8 7 7.5 2018-05-10 0 0 0 8 8

I found this article that helps, but I'm missing how to calculate what the age was based on the date.

https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/

4 REPLIES 4
Super User

Re: Average age of a Case over time

Hi,

How does one have to interpret a blank in the End column?  Should that be Today's date?

Frequent Visitor

Re: Average age of a Case over time

Correct. If a case is still open, it's age as of today is calculated using today's date.

Frequent Visitor

Re: Average age of a Case over time

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.

Highlighted
Super User

Hi,