Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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/
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.
Hi,
How does one have to interpret a blank in the End column? Should that be Today's date?
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.
Thanks in Advance!
Hi,
I do not understand your requirement.
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.
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:
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))))
Please find the sample dataset:
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!
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.
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.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |