Forum Discussion
Anonymous
7 years agoNot applicable
DateDiff between different stages
Hi, community I'm trying to calculate the average sales cycle length. In my case, I'm trying to make a difference between data from different rows. For example, the opportunity 0060Y00000J1tM...
- Anonymous7 years ago
Hi,
I finally find a way to do it I think there is others way to simplify the formula but it's working.
Many thanks for your contribution.
Days Challenge = IF ( 'Opportunity Field History'[OldValue] = "Challenge"; DATEDIFF ( CALCULATE ( MIN ( 'Opportunity Field History'[CreatedDate] ); ALLEXCEPT ( 'Opportunity Field History'; 'Opportunity Field History'[OpportunityId] ); 'Opportunity Field History'[Field] = "created" ); 'Opportunity Field History'[CreatedDate]; DAY ); IF ( 'Opportunity Field History'[OldValue] = "Closing"; DATEDIFF ( CALCULATE ( MIN ( 'Opportunity Field History'[CreatedDate] ); ALLEXCEPT ( 'Opportunity Field History'; 'Opportunity Field History'[OpportunityId] ); 'Opportunity Field History'[NewValue] = "Closing" ); 'Opportunity Field History'[CreatedDate]; DAY ); IF ( 'Opportunity Field History'[OldValue] = "Solution"; DATEDIFF ( CALCULATE ( MIN ( 'Opportunity Field History'[CreatedDate] ); ALLEXCEPT ( 'Opportunity Field History'; 'Opportunity Field History'[OpportunityId] ); 'Opportunity Field History'[NewValue] = "Solution" ); 'Opportunity Field History'[CreatedDate]; DAY ); IF ( 'Opportunity Field History'[OldValue] = "Value"; DATEDIFF ( CALCULATE ( MIN ( 'Opportunity Field History'[CreatedDate] ); ALLEXCEPT ( 'Opportunity Field History'; 'Opportunity Field History'[OpportunityId] ); 'Opportunity Field History'[NewValue] = "Value" ); 'Opportunity Field History'[CreatedDate]; DAY ); BLANK () ) ) ) )
Anonymous
7 years agoNot applicable
How is a cycle defined? Since you can have opportunities in different stages, one has to know what terminates an opportunity in order to calculate its cycle length. Opportunities that have not been terminated should not have a cycle length. Or am I missing anything?
Best
Darek
Best
Darek
- Anonymous7 years agoNot applicable
Hi Darek,
What I'm after is the cycle length per stage, so the average number of days an opportunity is staying in a specific stage.
Many Thanks