Networkdays

[drake]

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 

[mattbrice]

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

[drake]

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 · Accepted solution]

@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]

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