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/

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

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
Helper I

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.

Super User

Hi,

I do not understand your requirement.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Super User

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
Helper I

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:

What i am getting is as below:

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

 Complaint Nbr Created date Custfirst contact date Analysis Date Credit Date Closed Date Status Age C1 1/10/2019 1/10/2019 Open 0 C2 1/14/2019 1/14/2019 3/15/2019 4/22/2019 3/15/2019 Closed 60 C3 1/15/2019 1/15/2019 7/27/2020 1/25/2019 Closed 10 C4 1/16/2019 1/16/2019 1/21/2019 2/25/2019 1/21/2019 Closed 5 C5 1/17/2019 1/17/2019 Open 0 C6 1/22/2019 1/22/2019 Open 0 C7 1/28/2019 1/28/2019 Open 0 C8 1/28/2019 1/28/2019 Open 0 C9 1/28/2019 1/28/2019 2/11/2019 10/7/2019 2/26/2019 Closed 14 C10 2/5/2019 2/5/2019 5/2/2019 2/7/2019 5/2/2019 Closed 86 C11 2/14/2019 2/14/2019 2/27/2019 2/27/2019 Closed 13 C12 2/14/2019 2/14/2019 2/15/2019 2/18/2019 Closed 4 C13 2/14/2019 2/14/2019 2/15/2019 2/18/2019 Closed 4 C14 2/14/2019 2/14/2019 3/25/2019 2/18/2019 Closed 4 C15 2/18/2019 2/18/2019 5/2/2019 6/3/2019 5/2/2019 Closed 73 C16 2/18/2019 2/18/2019 5/2/2019 6/3/2019 5/2/2019 Closed 73 C17 2/25/2019 2/25/2019 4/2/2019 2/26/2019 Closed 1

Expected output is Age column.

Please let me know if anything else is needed.

Thanks!

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com