Date difference between rows

[Anonymous]

Yo everyone, how are you doing :)?

I am trying to measure avg duration between different stages at my work flow.

Im using salesforce field history data and the table build like that:

I Would like to find the avg duration of each ParentID(foregin key) and between specific stages. for example:

Whats the datediff between

Created date of ParentID = a2p..... ,oldValue = New, NewValue = Assignted &
Created date of ParentID = a2p..... ,oldValue = Assignted, NewValue = In progress.

What ive done so far:

Measure #1 =

var Type1 = CALCULATE(max('History: Work'[CreatedDate]),'History: Work'[OldValue] = "New",'History: Work'[NewValue] = "Assignted",'History: Work'[Work.agf__Type__c] = "User Story")

var Type2 = CALCULATE(max('History: Work'[CreatedDate]),'History: Work'[OldValue] = "Assignted",'History: Work'[NewValue] = " In Progress",'History: Work'[Work.agf__Type__c] = "User Story")

return

DATEDIFF(TYPE1,TYPE2,DAY)

Unfortunatly, I cant calculate the Average duration in that way.

Any suggestions?

[Anonymous]

@jdbuchanan71 Thank you so much dude! it worked 🙂

[jdbuchanan71 · Accepted solution]

@Anonymous 

Try createing an average measure like this.

Average Measure = AVERAGEX ( VALUES ( 'History: Work'[ParentID] ), [Measure #1] )