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

 

10 REPLIES 10
Frequent Visitor

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 III
Super User III

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur : i am new to power BI and I have been struggling to get average age of complaint over time. in my case, if closed date is blank, i need to replace it with another date as below. i have a separate date table and complaint table. How can we relate this with date table?

 

A- Analysis Date

B- ClosedDate

C - Credit Date

D- created date

 

expected Avg Age is the last column.. this is without involving date table. i.e,

Days old = if( Isblank(A)&& is blank(B) && isblank(C), 0 , if(isblank(A),Datediff(D-B), if(isblank(A) && isblank(B), Datediff(D-C), Datediff(D-A))))

 

How can i derive this formula in dax and calculate average age? Kindly help me on the same.

 

comlpaint sample data.png

 

Thanks in Advance!

Hi,

I do not understand your requirement.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur :

 

I need to calculate average age of tickets over time. i have a tickets table and a date table separately. (no active join)

 

Avg age  =  Avg(if(isblank(analysis date), Datediiff(Created date,Closed Date), if(isblank(anlaysis date)&& isblank(closed date), datediff(created date,credit date),datediff(createddate,analysisdate).

 

I need to implement a line graph for avg age with created date as x-axis.

 

Thanks in advance!

Hi,

I will not be able to help you like this.  Share a dataset which i can paste in MS Excel and on that datset, show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur ,

 

I need to find the average age of complaints by month. i have complaint table and date table and there is no join between the 2 tables. i am already having a combined column & line chart where i am showing New, WIP and Closed complaints as column and i need to show the trend of average age of complaints as a line. The sample chart is as below:

Expectced trend line:

 

2020-10-05_11h16_35.png

 

What i am getting is as below:

 

2020-10-05_11h00_39.png

 

Here, the x-axis is from date table. when i tried to use the age in the chart, i am getting a straight line for average and that is not correct.

 

The expected average age formula(computed in excel) is 

c2 - Cust contact date

D2 - Analysis Date

E2 - Credit Date

F2 - Closed Date

Age = IF(AND(ISBLANK(D2),ISBLANK(E2),ISBLANK(F2)),0,IF(ISBLANK(D2),DAYS(F2,C2),IF(ISBLANK(F2),DAYS(E2,C2),DAYS(D2,C2))))

Please find the sample dataset:

 

Complaint NbrCreated dateCustfirst contact dateAnalysis DateCredit DateClosed DateStatusAge
C11/10/20191/10/2019   Open0
C21/14/20191/14/20193/15/20194/22/20193/15/2019Closed60
C31/15/20191/15/2019 7/27/20201/25/2019Closed10
C41/16/20191/16/20191/21/20192/25/20191/21/2019Closed5
C51/17/20191/17/2019   Open0
C61/22/20191/22/2019   Open0
C71/28/20191/28/2019   Open0
C81/28/20191/28/2019   Open0
C91/28/20191/28/20192/11/201910/7/20192/26/2019Closed14
C102/5/20192/5/20195/2/20192/7/20195/2/2019Closed86
C112/14/20192/14/2019 2/27/20192/27/2019Closed13
C122/14/20192/14/2019 2/15/20192/18/2019Closed4
C132/14/20192/14/2019 2/15/20192/18/2019Closed4
C142/14/20192/14/2019 3/25/20192/18/2019Closed4
C152/18/20192/18/20195/2/20196/3/20195/2/2019Closed73
C162/18/20192/18/20195/2/20196/3/20195/2/2019Closed73
C172/25/20192/25/2019 4/2/20192/26/2019Closed1

 

Expected output is Age column.

Please let me know if anything else is needed.

 

Thanks!

Hi,

This calculated column formula works

=if(Data[Status]="Open",0,if(ISBLANK(Data[Analysis Date]),[Closed Date]-[Custfirst contact date],if(ISBLANK(Data[Closed Date]),[Credit Date]-[Custfirst contact date],[Analysis Date]-[Custfirst contact date])))

Hope this helps.

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

 

You may download my PBI file from here.

 

Hope this helps.

 

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors