Create graph with average time between 2 tasks

[romovaro]

HI

I need to create a graph showing CID with average time (Using the date in TAsk Completed Date) between 

task = Greenlight Checks (and task_status Closed)

&

task = IPM handoff (and task_status Closed)

The filter I need to use is that the Task = Pre engagement process had to be closed by the Pre Engagement Team.

The table look like this one below:

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

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?

I started with the formula below....

GreenLights-IpmHandoff =

VAR MeetsFirstRequirement =

CALCULATE (

COUNT ( 'BPMS LOF Report'[CID] ),

FILTER (

'BPMS LOF Report',

'BPMS LOF Report'[TASK] = "Pre engagement Process"

&& 'BPMS LOF Report'[TASK_STATUS] = "Closed" )

&& '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

Thanks,

[amitchandak]

@romovaro ,

you can join close date with date table and can use slicer and time intelligence for current month

for Avg day

Avg days = average(Table, datediff(Table[Start date], Table[End Date], day) )

MTD  = calculate([Avg Days], datesmtd('Date'[Date])

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs