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

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