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,

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
Icon for Microsoft Employee rankMicrosoft 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
    Icon for Advocate I rankAdvocate I
    9 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: