Create graph with average time between 2 tasks

Question

HI&nbsp;I need to create a graph showing CID with average time (Using the date in TAsk Completed Date) between&nbsp;task = Greenlight Checks (and task_status Closed) &amp; task = IPM handoff (and task_status Closed)&nbsp;The filter I need to use is that the Task = Pre engagement process had to be closed by the Pre Engagement Team.&nbsp;The table look like this one below:&nbsp;CIDINSTANCE_NUMBERTASKTASK_ASSIGNED_ONTASK_STATUSTASK_COMPLETED_DATETASKACTUALPERFORMER10005221233917IPM HandOff18-FEB-22&nbsp;Closed18-FEB-22&nbsp;Will Smith10005841234576IPM HandOff18-FEB-22&nbsp;Closed18-FEB-22&nbsp;Max Planck10007811272914Greenlight Checks16-FEB-22&nbsp;Closed18-FEB-22&nbsp;Maahi Olenik10007621271607Greenlight Checks15-FEB-22&nbsp;Closed16-FEB-22&nbsp;Nilesh Max10005421231151IPM HandOff11-FEB-22&nbsp;Closed11-FEB-22&nbsp;Will Smith10006621267552Greenlight Checks11-FEB-22&nbsp;Closed11-FEB-22&nbsp;Max Planck10005011216751IPM HandOff09-FEB-22&nbsp;Closed18-FEB-22&nbsp;Maahi Olenik10007221264204IPM HandOff08-FEB-22&nbsp;Closed08-FEB-22&nbsp;Nilesh Max10007221264204Pre Engagement Process08-FEB-22&nbsp;Closed08-FEB-22&nbsp;Will Smith10007221264204Greenlight Checks07-FEB-22&nbsp;Closed08-FEB-22&nbsp;Max Planck10007211264253Greenlight Checks07-FEB-22&nbsp;Closed08-FEB-22&nbsp;Maahi Olenik10006041263733Greenlight Checks07-FEB-22&nbsp;Closed20-FEB-22&nbsp;Nilesh Max10006051261846Greenlight Checks04-FEB-22&nbsp;Closed07-FEB-22&nbsp;Will Smith10002041153243IPM HandOff04-FEB-22&nbsp;Closed04-FEB-22&nbsp;Max Planck10007021261811Pre Engagement Process04-FEB-22&nbsp;Closed18-FEB-22&nbsp;Maahi Olenik&nbsp;I don't know how to create the formula to get that and combined the formula with the task completed month and get the average days. ANy help?&nbsp;I started with the formula below....&nbsp;GreenLights-IpmHandoff = VAR MeetsFirstRequirement =CALCULATE (COUNT ( 'BPMS LOF Report'[CID] ),FILTER ('BPMS LOF Report','BPMS LOF Report'[TASK] = "Pre engagement Process"&amp;&amp; 'BPMS LOF Report'[TASK_STATUS] = "Closed" )&amp;&amp; 'BPMS LOF Report'[TASKACTUALPERFORMER] = "John Smith"|| 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Max Planck"|| 'BPMS LOF Report'[TASKACTUALPERFORMER] = "Will SMith")VAR MeetsBothRequirements =CALCULATETABLE(VALUES( 'BPMS LOF Report'[CID] ),MeetsFirstRequirement,'BPMS LOF Report'[TASK_STATUS] = "Closed",'BPMS LOF Report'[TASK]IN { "Greenlights Checks", "IPM HandOff" })RETURN MeetsBothRequirements&nbsp;&nbsp;Thanks,

amitchandak · Answer

romovaro , 
you can join close date with date table and can use slicer and time intelligence for current month
for Avg day
&nbsp;
Avg days = average(Table, datediff(Table[Start date], Table[End Date], day) )
&nbsp;
MTD&nbsp; = calculate([Avg Days], datesmtd('Date'[Date])
&nbsp;
Power BI — Month on Month with or Without Time Intelligence https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090ehttps://www.youtube.com/watch?v=6LUBbvcxtKA
&nbsp;
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period, Custom Period till date: https://youtu.be/aU2aKbnHuWs

Forum Discussion

Create graph with average time between 2 tasks

1 Reply

Related content

Creating an average

Trouble Creating Average of Daily Averages - While Keeping it Filterable

Average and Moving Average

Weekly Average

Accumulated average

CID	INSTANCE_NUMBER	TASK	TASK_ASSIGNED_ON	TASK_STATUS	TASK_COMPLETED_DATE	TASKACTUALPERFORMER
1000522	1233917	IPM HandOff	18-FEB-22	Closed	18-FEB-22	Will Smith
1000584	1234576	IPM HandOff	18-FEB-22	Closed	18-FEB-22	Max Planck
1000781	1272914	Greenlight Checks	16-FEB-22	Closed	18-FEB-22	Maahi Olenik
1000762	1271607	Greenlight Checks	15-FEB-22	Closed	16-FEB-22	Nilesh Max
1000542	1231151	IPM HandOff	11-FEB-22	Closed	11-FEB-22	Will Smith
1000662	1267552	Greenlight Checks	11-FEB-22	Closed	11-FEB-22	Max Planck
1000501	1216751	IPM HandOff	09-FEB-22	Closed	18-FEB-22	Maahi Olenik
1000722	1264204	IPM HandOff	08-FEB-22	Closed	08-FEB-22	Nilesh Max
1000722	1264204	Pre Engagement Process	08-FEB-22	Closed	08-FEB-22	Will Smith
1000722	1264204	Greenlight Checks	07-FEB-22	Closed	08-FEB-22	Max Planck
1000721	1264253	Greenlight Checks	07-FEB-22	Closed	08-FEB-22	Maahi Olenik
1000604	1263733	Greenlight Checks	07-FEB-22	Closed	20-FEB-22	Nilesh Max
1000605	1261846	Greenlight Checks	04-FEB-22	Closed	07-FEB-22	Will Smith
1000204	1153243	IPM HandOff	04-FEB-22	Closed	04-FEB-22	Max Planck
1000702	1261811	Pre Engagement Process	04-FEB-22	Closed	18-FEB-22	Maahi Olenik