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
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 ()
)
)
)
)