Forum Discussion

drake's avatar
drake
Frequent Visitor
9 years ago
Solved

Networkdays

HI Guys new to this so trying to translate my excel skills to DAX !

 

I have two date columns and i want the working days between

iecolum A 1-june-2016   and column B 30-December 2016

current using the DATEDIFF ftn which is a good starting point

 

What im looking for is help with is :

a) how to deal with a a missing date - i want 0 for that row

b)if the date in colum A is greater thab B to rerun a negaive value

 

iwoould like to see something like that below giving results in column C

     A                  B                   C

1/1/16          14/1/16           10 [ Working days]

1/1/16           BLANK              0

14/1/16         1/1/16            -10

 

Hope thats clear - thanks 

 

Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    drake

     

    In this scenario, you need to have a full calendar date table, then add a column to tag if the date is working day.

     

    IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1)

     

     

    Then you can create a calculated column like below:

     

    NetWorkingDays =
IF (
    ISBLANK ( DateRange[StartDate] ) || ISBLANK ( DateRange[EndDate] ),
    0,
    IF (
        DateRange[StartDate] <= DateRange[EndDate],
        CALCULATE (
            COUNT ( 'Calendar'[IsWorkingDay] ),
            DATESBETWEEN ( 'Calendar'[Date], DateRange[StartDate], DateRange[EndDate] )
        ),
        - CALCULATE (
            COUNT ( 'Calendar'[IsWorkingDay] ),
            DATESBETWEEN ( 'Calendar'[Date], DateRange[EndDate], DateRange[StartDate] )
        )
    )
)

     

     

    Regards,

4 Replies

  • mattbrice's avatar
    mattbrice
    Solution Sage
    9 years ago

    Networkdays = IF ( ISBLANK ( table[Date A] ) || ISBLANK ( table[Date B] ), 0, DATEDIFF ( table[Date A], table[Date B], day ) )

    • drake's avatar
      drake
      Frequent Visitor
      9 years ago

      thanks - i had this but im not sure if your formula - [which is cleverer than mine !]  will return network days or calander days ?

       

       

      APP 2 Posting = if(OR
      ('Data'[First Fully Approved Date]=0,'Data'[First Sourced Date]=0)
      ,0,
      (DATEDIFF
      ('Data'[First Fully Approved Date].[Date],'Data'[First Sourced Date].[Date],DAY)
      ))

      • v-sihou-msft's avatar
        v-sihou-msft
        Microsoft Employee
        9 years ago

        drake

         

        In this scenario, you need to have a full calendar date table, then add a column to tag if the date is working day.

         

        IsWorkingDay = IF(WEEKDAY('Calendar'[Date],2)>5,0,1)

         

         

        Then you can create a calculated column like below:

         

        NetWorkingDays =
IF (
    ISBLANK ( DateRange[StartDate] ) || ISBLANK ( DateRange[EndDate] ),
    0,
    IF (
        DateRange[StartDate] <= DateRange[EndDate],
        CALCULATE (
            COUNT ( 'Calendar'[IsWorkingDay] ),
            DATESBETWEEN ( 'Calendar'[Date], DateRange[StartDate], DateRange[EndDate] )
        ),
        - CALCULATE (
            COUNT ( 'Calendar'[IsWorkingDay] ),
            DATESBETWEEN ( 'Calendar'[Date], DateRange[EndDate], DateRange[StartDate] )
        )
    )
)

         

         

        Regards,