cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not 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 0060Y00000J1tMvQAJ was created on 19/03/2018 (Each new opportunities automatically takes the Challenge stage). This opportunity staid 490 days in Challenge then moved to the stage Value on 22/07/2019, and 3 days later moved to Solution. As you can see an opportunity doesn't have the requirement to go through all the stage (Challenge to Solution, Challenge to Closed Won).

 

Many thanks. Smiley Happy

 

Capture.PNG

1 ACCEPTED SOLUTION
Anonymous
Not 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 ()
            )
        )
    )
)

View solution in original post

8 REPLIES 8
Anonymous
Not 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 ()
            )
        )
    )
)

View solution in original post

Anonymous
Not 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
Anonymous
Not 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 

Stachu
Community Champion
Community Champion

try this code for the measure

Measure =
VAR __OpportunityStartEnd =
    GROUPBY (
        'Opportunites',
        Opportunites[OpportunityId],
        "Start", MINX ( CURRENTGROUP (), 'Opportunites'[CreatedDate] ),
        "End", MAXX ( CURRENTGROUP (), 'Opportunites'[CreatedDate] )
    )
VAR __OpportunityLength =
    ADDCOLUMNS ( __OpportunityStartEnd, "Length", [End] - [Start] )
RETURN
    AVERAGEX ( __OpportunityLength, [Length] )


Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

Anonymous
Not applicable

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

 

Capture.PNG

Stachu
Community Champion
Community Champion

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?

 



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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.

 

2021-06-14 16_35_30-Window.png

Any suggestions? 

My post is here: DATEDIFF from nonconsecutive rows 

This helps. Thank you @Stachu 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.