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.

 

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

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,158 guests
Please welcome our newest community members: