Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DATEDIFF on single column

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:

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    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

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

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

      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's avatar
        v-alq-msft
        Community Support
        6 years ago

        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