Forum Discussion
Date difference between rows
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
Try createing an average measure like this.
Average Measure = AVERAGEX ( VALUES ( 'History: Work'[ParentID] ), [Measure #1] )
2 Replies
Anonymous
Try createing an average measure like this.
Average Measure = AVERAGEX ( VALUES ( 'History: Work'[ParentID] ), [Measure #1] )jdbuchanan71 Thank you so much dude! it worked 🙂