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 ...
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

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