Calculating Duration between two or more dates in one column based on a condition

[Anonymous]

I know this type of question has been asked repeatedly and I have spent hours trying to apply it to my situation but have failed so far. 

Here is what the data model looks like.  There are multiple different type of fields and new values but the ones in the example are what I am concerned about. 

--Program Parent ID--	--History   ID--	--New Value--	---Field	--Date--
123	346	Created	Implementation Status	6/1/2020
123	789	Kickoff	Implementation Status	6/3/2020
123	101	Handoff	Implementation Status	6/7/2020

In the past the data model would look like this and it would be simple datediff calcs:

-Program Parent ID--	-Created Date-	-Kickoff Date-	-Handoff Date-
123	6/1/2020	6/3/2020	6/7/2020

Ultimately for every Program (Parent ID) I want to calculate duration from created to kickoff, or kickoff to handoff or created to handoff for example. This is what I have tried https://www.youtube.com/watch?v=jYvr4histgY  but her calc wasn't working for me. 

[lbendlin · Accepted solution]

Here's a measure for one of the questions, it also illustrates the general approach

C to K = 
var p=SELECTEDVALUE('Table'[--Program Parent ID--])
var c=CALCULATE(max('Table'[--Date--]),all('Table'),'Table'[--Program Parent ID--]=p,'Table'[--New Value--]="Created")
var k=CALCULATE(max('Table'[--Date--]),all('Table'),'Table'[--Program Parent ID--]=p,'Table'[--New Value--]="Kickoff")
var h=CALCULATE(max('Table'[--Date--]),all('Table'),'Table'[--Program Parent ID--]=p,'Table'[--New Value--]="Handoff")
return datediff(c,k,DAY)

[bobbyilham]

Hi @Anonymous 

Have you tried this formula:

Kickoff to Created =

VAR kickoff =
CALCULATE
(
   MAX(table[Date]),
   FILTER
   (
      ALLEXCEPT(table, table[Program Parent ID]),
      table[New Value] = "Kickoff"
   )
)

VAR created =
CALCULATE
(
   MAX(table[Date]),
   FILTER
   (
      ALLEXCEPT(table, table[Program Parent ID]),
      table[New Value] = "Created"
   )
)

RETURN
DATEDIFF(kickoff, created, DAY)