cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KenvM Frequent Visitor
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:

 

CaseStartEnd
100012018-05-012018-05-05
100022018-05-032018-05-08
100032018-05-012018-05-09
100042018-05-02 

 


I want the following results: 

 

Date10001100021000310004Average
2018-05-0100000
2018-05-0210101
2018-05-0320211.666667
2018-05-0431322.25
2018-05-0542433.25
2018-05-0603544
2018-05-0704655
2018-05-0805766
2018-05-0900877.5
2018-05-1000088


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
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?

KenvM Frequent Visitor
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.

KenvM Frequent Visitor
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
Super User

Re: Average age of a Case over time

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png