DATEDIFF on single column

[Anonymous]

I have a table that tracks the history of opportunities through different phases. I am trying to figure out how to create a measure that will tell me the difference in days (excluding weekends) that an opportunity spent in a phase:

So for the above, I know this opportunity was in 'Discovery' from 9/26-10/9. Then in 'Presentation' from 10/9-11/14, etc. How can I calculate the difference in days for each StageName? Then, how can I get the average # of days for each StageName? The above is just one filtered opportunity but I would like to get an average number of days for each StageName across multiple opportunities.

I have somewhat gotten started but the solution I've found was centered around making it a measure which if I'm not mistaken you cannot average a measure:

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce the scenario.

You can create a measure as follows.

AvgDayEachStage =

 DIVIDE(

    COUNTROWS(FILTER( CALENDAR( CALCULATE(

                        MIN('Opportunity History'[CreateDate]),

                        ALLEXCEPT('Opportunity History',

                                  'Opportunity History'[StageName])),CALCULATE(

                        MAX('Opportunity History'[CreateDate]),

                        ALLEXCEPT('Opportunity History',

                                  'Opportunity History'[StageName]))),

                                  not WEEKDAY([Date]) in {1,7}

                                  )

                                  )

                                  ,CALCULATE(COUNTROWS('Opportunity History'),ALLEXCEPT('Opportunity History','Opportunity History'[StageName])),0)

Result:

If I misunderstand your thought, please show me your detailed sample data and expected result. I am glad to solve the problem for you.

Best Regards,

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

hi @v-alq-msft that was close but not quite what I needed. My apologies, I likely did not explain it well enough. So for each stage name, I need to know the difference between the earliest date and the latest date of the stagename:

So for Discovery: how many work days between 9/26 and 10/9

           Presentation: how many work days between 10/9 and 11/14

           Closing: how many work days between 11/14 and 11/21

I have formulas that return the earliest date and the latest date for each stagename and I've plugged those into a DateDiff calculation but it does not exclude work days. So for the above I have:

Which the Date Diff StageName formula is:

So it is calculating the days correctly but I need it to exclude weekends.

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

It is unavailble to use DateDiff() function if you want to calculate workdays. You can try the following measure which excludes weekends.

Date Diff StageName = 
COUNTROWS (
        FILTER (
            CALENDAR (
                CALCULATE (
                    MIN ( 'Opportunity History'[CreateDate] ),
                    ALLEXCEPT ( 'Opportunity History', 'Opportunity History'[StageName] )
                ),
                CALCULATE (
                    MAX ( 'Opportunity History'[CreateDate] ),
                    ALLEXCEPT ( 'Opportunity History', 'Opportunity History'[StageName] )
                )
            ),
            NOT WEEKDAY ( [Date] ) IN { 1, 7 }
            
        )
    )

Result:

Best Regards

Allan