Forum Discussion
Time in Stage Calculation
Hi nckpedersen ,
For this use case, I'd create an index column to sort each stage by opportunityid
Index by Opportunity = RANKX ( FILTER ( 'Table', 'Table'[OpportunityID] = EARLIER ( 'Table'[OpportunityID] ) ), 'Table'[CreatedDate], , asc, DENSE )And then I'd create column to get the time difference
Time Difference = VAR _prev = CALCULATE ( MAX ( 'Table'[CreatedDate] ), FILTER ( 'Table', 'Table'[OpportunityID] = EARLIER ( 'Table'[OpportunityID] ) && 'Table'[Index by Opportunity] = EARLIER ( 'Table'[Index by Opportunity] ) - 1 ) ) RETURN IF ( _prev = BLANK (), BLANK (), 'Table'[CreatedDate] - _prev )The above formula will return decimal numbers wherein anything less than one is a portion of a day. Create a measure a measure to convert the difference into number of days/hours/minutes
Sample result
Please see attached pbix for reference
Hi nckpedersen ,
For this use case, I'd create an index column to sort each stage by opportunityid
Index by Opportunity =
RANKX (
FILTER ( 'Table', 'Table'[OpportunityID] = EARLIER ( 'Table'[OpportunityID] ) ),
'Table'[CreatedDate],
,
asc,
DENSE
)
And then I'd create column to get the time difference
Time Difference =
VAR _prev =
CALCULATE (
MAX ( 'Table'[CreatedDate] ),
FILTER (
'Table',
'Table'[OpportunityID] = EARLIER ( 'Table'[OpportunityID] )
&& 'Table'[Index by Opportunity]
= EARLIER ( 'Table'[Index by Opportunity] ) - 1
)
)
RETURN
IF ( _prev = BLANK (), BLANK (), 'Table'[CreatedDate] - _prev )
The above formula will return decimal numbers wherein anything less than one is a portion of a day. Create a measure a measure to convert the difference into number of days/hours/minutes
Sample result
Please see attached pbix for reference
You're incredible, thank you! I've always been tripped up by the 'Earlier' function - really interesting to see it come in here and do the heavy lifting. I was trying to replicate with an "All Except" approach but couldn't use an expression.
This is amazing work, thank you again.