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.
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.