Forum Discussion

nckpedersen's avatar
nckpedersen
Helper I
3 years ago
Solved

Time in Stage Calculation

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. 

Need Help
  • danextian's avatar
    danextian
    3 years ago

    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

2 Replies

  • danextian's avatar
    danextian
    Super User
    3 years ago

    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's avatar
      nckpedersen
      Helper I
      3 years ago

      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.