Time in Stage Calculation

[nckpedersen]

I'm trying to determine how long sales opportunities are staying within pre-designated opportunity stages. While Salesforce does give me a change log that allows me to approximate stages, I'm missing a crucial piece in calculating the 'jump' to the next stage. For example, here's one opportunity: 

I've created a calculated column for each stage with formula:

Time Lead = calculate(MAX('Opportunity History'[CreatedDate]),filter('Opportunity History','Opportunity History'[OpportunityId]='Lead Opps'[ID] && 'Opportunity History'[StageName]="Lead Opportunity"))-calculate(MIN('Opportunity History'[CreatedDate]),filter('Opportunity History','Opportunity History'[OpportunityId]='Lead Opps'[ID] && 'Opportunity History'[StageName]="Lead Opportunity"))

That gives me: 

Is it possible to get the value right after Max? That would close the gaps. 

[danextian · Accepted solution]

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

[nckpedersen]

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.