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

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,

      • praseejbk's avatar
        praseejbk
        Advocate I
        8 years ago

        Hi

         

        Thanks a lot  for your formula, but there is a small issue I found that instead of using COUNT I used SUM now it is working fine:smileyhappy: