Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
  • Anonymous's avatar
    Anonymous
    7 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's avatar
Anonymous
Not applicable
7 years ago

Hi Stachu, 

Many thanks for your help but it's not working. I did it using excel and for each opportunity ID, I have the number of days staid per stage. For that I was using this formula:

=IF(D427="Value";MAXIFS(F:F;C:C;C427;D:D;D427)-MAXIFS(F:F;C:C;C427;E:E;D427);"")

 

Many thanks

 

Stachu's avatar
Stachu
Community Champion
7 years ago

I think the easiest way to do it is to calculate the cycle length in the calculated column, like this:

Cycle Length =
VAR __OpportunityId = 'Opportunites'[OpportunityId]
VAR __CreatedDate = 'Opportunites'[CreatedDate]
VAR __PreviousStageDate =
    CALCULATE (
        MIN ( 'Opportunites'[CreatedDate] ),
        FILTER (
            ALL ( 'Opportunites' ),
            'Opportunites'[OpportunityId] = __OpportunityId
                && 'Opportunites'[CreatedDate] > __CreatedDate
        )
    )
VAR __PreviousStageDateNoBlanks =
    IF ( ISBLANK ( __PreviousStageDate ), __CreatedDate, __PreviousStageDate )
RETURN
    __PreviousStageDateNoBlanks - __CreatedDate

and then calculate the average based on that  (I filter out 0 length cycles, as the end of the process is a point in time, and therefore doesn't have length):

Cycle Avg Length =
VAR __OpportunityCycleLengthNo0s =
    FILTER ( 'Opportunites', 'Opportunites'[Cycle Length] <> 0 )
VAR __AverageCyclePerOpportunity =
    GROUPBY (
        __OpportunityCycleLengthNo0s,
        Opportunites[OpportunityId],
        "Avg", AVERAGEX ( CURRENTGROUP (), 'Opportunites'[Cycle Length] )
    )
RETURN
    AVERAGEX ( __AverageCyclePerOpportunity, [Avg] )

I used the average to average the averages of multiples IDs (this only shows when you have multiple IDs in the filter context, e.g. in the Total line), is this your intended aggregation on this level?

 

  • nanma94's avatar
    nanma94
    Helper III
    6 years ago

    This helps. Thank you Stachu 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Stachu - I'm trying to apply this to a similar situation I'm having.  I follwed your first step above and it worked, but the return value is a date, not a time difference.  I'd like to calc the DATEDIFF between a previous row value that has the same "OpportunityID".  In my case, the Category is called ProjectID.

     

    Any suggestions? 

    My post is here: DATEDIFF from nonconsecutive rows