Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Is it possible to get the value right after Max? That would close the gaps.
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
105 | |
78 | |
67 | |
63 |
User | Count |
---|---|
144 | |
107 | |
105 | |
82 | |
69 |