Week Ending Average Age of Open Requests

[dogt1225]

Hi All, 

I am looking to create a bar chart on PowerBI that shows the Week Ending Average Age of Open Requests.

For my company the Fiscal week begins on a Saturday and End on a Friday. Also the Fiscal Year begins on the 5th Saturday of the Calendar Year. (Example for FY17, it begins January 30th, 2016) The dates in my dataset begins in 2016 and is currently ongoing.
So my problem is I need to calcualate for the week ending that Friday of ALL the OPEN requests, what is the average age?
Here is what I have so far, but Ive noticed that instead of using TODAY() I will need to use the week ending date for that week. Any ideas on how to solve this? Please let me know if there is any missing details in my explanation above. Thank you!

Fiscal Week = CONCATENATE("Wk",
  IF(
  'Calendar'[Week Number] <= 5, 
  'Calendar'[Week Number] + 47, 
  'Calendar'[Week Number] - 5
  )
)   

Avg Age = [Cumulative Sum of Age (Open Request)] / [Total Open Request]

Cumulative Sum of Age (Open Request) = 
CALCULATE(
  SUMX(
   FILTER(DataTable, DataTable[Submitted_Date] <= MAX('Calendar'[Date]) && 
   (ISBLANK(DataTable[Date Resolved]) || DataTable[Date Resolved] >                                                   
   MAX('Calendar'[Date]))), DATEDIFF(DataTable[Submitted_Date], TODAY(), DAY)
  ),
  CROSSFILTER('Calendar'[Date], DataTable[Submitted_Date], None), 
  DataTable[Submission Type] <> BLANK(),
  DataTable[Submitted_Date] <> BLANK(),
  DataTable[Request Status] <> "Closed",
  DataTable[Request Status] <> "Resolved", 
  DataTable[Request Status] <> "Completed-Pending"
)

Total Open Request = 
CALCULATE(
  COUNT(DataTable[Submitted_Date]),
  DataTable[Submitted_Date] <> BLANK(), 
  DataTable[Submission Type] <> BLANK(),
  DataTable[Request Status] <> "Closed",
  DataTable[Request Status] <> "Resolved",
  DataTable[Request Status] <> "Completed-Pending", 
  FILTER(ALL('Calendar'), 'Calendar'[Date] <= MAX('Calendar'[Date]))
)

Sample Data:

ID	Submitted Date	Date Resolved	Request Status	Submission Type
1	2/17/2020	8/26/2020	Resolved	Idea
2	2/27/2020	8/27/2020	Closed	Idea
3	4/29/2020		Re-Assigned	Idea
4	4/30/2020		Re-Assigned	Idea
5	5/4/2020		Re-Assigned	Idea
6	5/7/2020		Re-Assigned	Idea
7	5/22/2020		Working	Idea
8	6/4/2020		Working	Idea
9	6/10/2020		Working	Idea
10	6/11/2020		Working	Idea
11	7/9/2020		Working	Idea
12	7/14/2020		Working	Idea
13	7/21/2020		Working	Idea
14	8/4/2020		Working	Process
15	8/5/2020		Working	Process
16	8/10/2020	8/24/2020	Completed-Pending	Process
17	8/11/2020	8/24/2020	Closed	Process
18	8/14/2020		Working	Process
19	8/19/2020		Working	Process
20	8/21/2020		Working	Process
21	8/24/2020		Working	Process
22	8/26/2020		Working	Idea
23	8/27/2020		Working	Idea
24	8/27/2020	8/28/2020	Resolved	Idea
25	2/9/2020		New	Idea
26	5/20/2020		New	Idea
27	7/4/2020		New	Idea
28	8/10/2020		New	Idea

[Greg_Deckler]

@dogt1225 Perhaps you can use this for Week Ending:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Ending/m-p/389293

[amitchandak]

@dogt1225 , if your problem is the week ending Friday. refer to this blog.

all types of the week are there

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

How to deal with once have correct week

https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Or video -https://youtu.be/e6Y-l_JtCq4

For what request that open by weekend you just need to display the data by week .

Refer to my HR blog for current Employee /Open Request would be the same

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

[dogt1225]

@amitchandak and @Greg_Deckler Thank you for the responses. 

I was able to create a Week Ending Date for my Calendar table, but I am still unsure how I will use that date to find the cumulative average for a given week.

@amitchandak Your link seems to go over comparing this week vs last week. Not quite what I am looking for. The math should be the Cumulative Sum of (Date Difference (Week End Selected - Submitted Date)) / Total Open requests for the week. Any ideas?

Week End Date = CALCULATE(
                    MIN([Date]), 
                    FILTER('Calendar', WEEKDAY([Date],2)=5 && 'Calendar'[Date]>=EARLIER('Calendar'[Date])
                    )
)

[Greg_Deckler · Accepted solution]

@dogt1225 OK, if you have a Week Ending, then you should be able to use the built-in Running Total Quick Measure potentially. Otherwise, potentially:

Cumulative Measure = 
  VAR __Current = MAX([Week Ending])
RETURN
  SUMX(FILTER(ALL('Table'),[Week Ending]<__Current),[Measure])

[Measure] should be whatever you are doing now to calculate it non-cumulatively. Mileage may vary. You may need an ADDCOLUMNS wrapping your FILTER or even a SUMMARIZE. But I think if you use this for your measure:

DATEDIFF(MAX(DataTable[Submitted_Date]), TODAY(), DAY)

If you can post your sample data with the Week Ending you calculated I can try to mock it up and test it and be specific.