Average Days to Resolve by Fiscal Week

[dogt1225]

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!

[Greg_Deckler · Accepted solution]

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

[v-shex-msft]

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

[amitchandak]

@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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...