I need a little bit of help with calculating the average age of open issues over time using DAX.
Using the sample data set:
I want the following results:
I found this article that helps, but I'm missing how to calculate what the age was based on the date.
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.
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
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!
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!
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|
Expected output is Age column.
Please let me know if anything else is needed.
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.
You may download my PBI file from here.
Hope this helps.