Forum Discussion
Average Days to Resolve by Fiscal Week
Hi All,
I am looking to create a bar chart for the Average Days to Resolve(ADR) a ticket per Fiscal Week and a line that has the Running Average. Exactly like the screen shot below.
ADR = cumulative sum of days to resolve / Total resolved ticket
I currently have a [Days to Resolve] field = DATEDIFF(Dataset[Submitted_Date], Dataset[Date Resolved], DAY). And I currently have a separate Calendar table that has Date, Fiscal Year, and Fiscal Week
Sample data set listed below Based on the sample data set, for the week of August 22 -August 28 2020 the ADR should be 58 days.
| ID | Submitted Date | Date Resolved |
| 1 | 2/17/2020 | 8/26/2020 |
| 2 | 2/27/2020 | 8/27/2020 |
| 3 | 4/29/2020 | 8/24/2020 |
| 4 | 4/30/2020 | 8/24/2020 |
| 5 | 5/4/2020 | 8/24/2020 |
| 6 | 5/7/2020 | 8/26/2020 |
| 7 | 5/22/2020 | 8/24/2020 |
| 8 | 6/4/2020 | 8/28/2020 |
| 9 | 6/10/2020 | 8/24/2020 |
| 10 | 6/11/2020 | 8/28/2020 |
| 11 | 7/9/2020 | 8/24/2020 |
| 12 | 7/14/2020 | 8/27/2020 |
| 13 | 7/21/2020 | 8/24/2020 |
| 14 | 8/4/2020 | 8/27/2020 |
| 15 | 8/5/2020 | 8/28/2020 |
| 16 | 8/10/2020 | 8/24/2020 |
| 17 | 8/11/2020 | 8/24/2020 |
| 18 | 8/14/2020 | 8/24/2020 |
| 19 | 8/19/2020 | 8/25/2020 |
| 20 | 8/21/2020 | 8/28/2020 |
| 21 | 8/24/2020 | 8/27/2020 |
| 22 | 8/26/2020 | 8/27/2020 |
| 23 | 8/27/2020 | 8/27/2020 |
| 24 | 8/27/2020 | 8/28/2020 |
| 25 | 2/9/2020 | 7/4/2020 |
| 26 | 5/20/2020 | 7/26/2020 |
| 27 | 7/4/2020 | 8/1/2020 |
| 28 | 8/10/2020 | 8/11/2020 |
Thank you in advance!
dogt1225 - I get 57.75 for my ADR for that week! 🙂
Anyway, take a look at the attached PBIX file below my sig, Page 16, Table 16, Measure 16:
Measure 16 = VAR __Table = ADDCOLUMNS( FILTER('Table (16)',[Date Resolved]<=MAX('Calendar'[Date]) && [Date Resolved]>=MIN('Calendar'[Date])), "Days",([Date Resolved] - [Submitted Date]) * 1. ) RETURN DIVIDE(SUMX(__Table,[Days]),COUNTROWS(__Table),0)
3 Replies
dogt1225 - I get 57.75 for my ADR for that week! 🙂
Anyway, take a look at the attached PBIX file below my sig, Page 16, Table 16, Measure 16:
Measure 16 = VAR __Table = ADDCOLUMNS( FILTER('Table (16)',[Date Resolved]<=MAX('Calendar'[Date]) && [Date Resolved]>=MIN('Calendar'[Date])), "Days",([Date Resolved] - [Submitted Date]) * 1. ) RETURN DIVIDE(SUMX(__Table,[Days]),COUNTROWS(__Table),0)dogt1225 , You might have to use Date Resolved for this. Join both dates with date table and use userelation
ADR = calculate(Divide(Sumx(Dataset,DATEDIFF(Dataset[Submitted_Date], Dataset[Date Resolved], DAY)),count(Table[ID])), userelation(Date[date],Table[Date Resolved]))
Refer
HI dogt1225,
You can take a look at following link to create a expand table with detail records of date ranges, then you simply filter on new table fields to get dynamic result:
Spread revenue across period based on start and end date, slice and dase this using different dates
Regards.
Xiaoxin Sheng
