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,

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: