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

 

Ashish_Mathur
Super User
Super User

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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors