Networkdays combined with IF measure

[vbatch]

Hello, 

I created a measure for networkdays, but when the days for my two fields match, the results aren't giving me 0 days difference, rather it is returning 1 day. The only instance where the result is 0 days is when the days being calculated is during a weekend. Below is the formula.

Column 1 = VAR sent_date = SELECTEDVALUE('sentdate'])

var contact_date = SELECTEDVALUE('contactdate'])

return

NETWORKDAYS('sentdate],'contactdate'],1)

To bypass this error where 0 days is not in all instances, I created a new column using an IF statement. 

Column 2 = IF('sentdate'] = 'contactdate'], "0")

This is returning the 0 for me, but it's only giving me some of the results. At the end of the above formula I also added in: 

Column 2 = IF('sentdate']='contactdate'], "0", 'column1')

but Power BI isn't defaulting to the networkdays in column1 under the else condition.

Is anyone familiar with how to built the networkdays formula where it will return 0 days when two dates match, regardless if it's a weekend day or not? And if not, is there another workaround to get this to work correctly? I've also tried a combination of a lookupvalue and IF, but I also cannot get that to work correctly. Any help is appreciated! 

[OwenAuger · Accepted solution]

Hello @vbatch 

NETWORKDAYS effectively counts work days occuring from StartDate to EndDate inclusive.

What is the exact rule you want to apply?

Is it to take the dates from StartDate to EndDate and return the difference between the earliest workday and latest workday?

Do these look correct? Otherwise please update to what you would expect

StartDate	EndDate	Difference	Calculation
Sun 23 July	Sun 23 July	0	No workdays => 0
Sun 23 July	Mon 24 July	0	24-Jul - 24-Jul
Mon 24 July	Mon 24 July	0	24-Jul - 24-Jul
Mon 24 July	Tue 25 July	1	25-Jul - 24-Jul
Mon 24 July	Wed 26 July	2	26-Jul - 24-Jul
Sun 23 July	Sun 30 July	4	28-Jul - 24-Jul

An expression that would produce the above is:

MAX ( 0, NETWORKDAYS ( <SentDate>, <ContactDate> ) - 1 )

Regards