NETWORKDAYS.INTL with Country as context

[olimilo]

So, I would like to compute for the number of working days with the Country as a contextual filter. As some of you might know, weekends vary on several countries (eg: Middle East countries like Jordan, UAE, etc.). It's easy to compute for that on Excel using NETWORKDAYS.INTL but that same function is not available on Power BI/DAX.

I'm already able to compute for the number of Non-Working Days with Country as a contextual filter:

NumberOfHolidays = 
	COUNTROWS(
		FILTER(
			All ( Holidays ),
			Holidays[Date] <= 'Active Projects'[CompleteByEndDate] &&
			Holidays[Date] >= 'Active Projects'[CompleteByStartDate] &&
			Holidays[Country] = 'Active Projects'[Country] &&
			Holidays[Isweekend] = FALSE()
		)
	)

My only problem is to get the number of working days (minus weekends) and then I'll just subtract the NumberOfHolidays from the result to get the total working days between DateA and DateB.

I've modeled my data like so:

I am able to get the days elapsed since CompleteByEndDate (deadline):

Days Elapsed (Deadline) = 
	SWITCH(
		TRUE(),
		'Active Projects'[CompleteByEndDate] < TODAY(), DATEDIFF('Active Projects'[CompleteByEndDate], TODAY(), DAY),
		'Active Projects'[CompleteByEndDate] > TODAY(), -1 * DATEDIFF(TODAY(), 'Active Projects'[CompleteByEndDate], DAY)
	)

But this does not differentiate between weekdays and weekends (it just gets the number of calendar days that have passed since the CompleteByEndDate.

I was hoping to use the Dates table on my formula in the Active Projects table but the Dates table isn't coming up when I type in the formula even when I try to make a measure or a calculated column and relate the two tables via [Date]<->[InspectionDate]. The error I'm getting is:

A single value for column 'Date' in table 'Dates' cannot be determined.
This can happen when a measure formula refers to a column that contains
many values without specifying an aggregation such as min, max, count, 
or sum to get a single result.

I'm a bit confused on what to do here, hopefully someone can direct me on how to solve this.

EDIT: Okay, so I've been experimenting on this over and I can't seem to get this DAX to work:

Basically, what I'm trying to achieve is to count all the dates from StartDate to EndDate and filter out the ones whose Dates[WeekdayNumber] is found on the Weekdays[WeekendNum]. For example, if the Country selected is China, it would look for 7 or 1 and not count those two. If it is Jordan, it would look for 6 or 7 (Fri/Sat) and not count the dates that fall on those WeekdayNumber.

I can get the correct weekend set using LOOKUPVALUE() but it seems I'm getting the error from the SEARCH() function.

NonWorkingDays = 
	COUNTROWS(
		FILTER(
			ALL(Dates),
			Dates[Date] >= 'Active Projects'[CompleteByStartDate] &&
			Dates[Date] <= 'Active Projects'[CompleteByEndDate] &&
			SEARCH(Dates[WeekdayNumber], LOOKUPVALUE(Weekdays[WeekendNum], Weekdays[Country], 'Active Projects'[Country])) <> 0
		)
	)

This is the error I'm getting: A single value for column 'WeekdayNumber' in table 'Dates' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

[v-jiascu-msft]

@olimilo

Hi,

First, we need to change one relationship.  Actually Holidays and Weekdays are the features of country. The relationship between Holidays and Dates isn’t proper. Let’s delete the relationship Holidays—Dates and create a new one. See the details in the picture 1. Change the Cross Filter Direction into “both” at the same time.

Second, create several measures.

According to test, you formula of NumberOfHolidays doesn’t work. Try this one please.

NumberOfHolidays =
COUNTROWS (
    FILTER (
        Holidays,
        Holidays[Country] = MIN ( 'Active Projects'[Country] )
            && Holidays[Date] >= MIN ( 'Active Projects'[CompleteByStartDate] )
            && Holidays[Date] <= MIN ( 'Active Projects'[CompleteByEndDate] )
            && Holidays[IsWeekend] = "False"
    )
)

AllWorkdays =
CALCULATE (
    COUNT ( 'Dates'[Date] ),
    FILTER (
        ALL ( 'Dates' ),
        'Dates'[Date] >= MIN ( 'Active Projects'[CompleteByStartDate] )
            && 'Dates'[Date] <= MIN ( 'Active Projects'[CompleteByEndDate] )
            && (
                NOT WEEKDAY ( 'Dates'[Date], 1 )
                    IN {
                            VALUE ( LEFT ( MIN ( Weekdays[WeekendNum] ), 1 ) ),
                            VALUE ( RIGHT ( MIN ( Weekdays[WeekendNum] ), 1 ) ) }
            )
    )
)
    - [NumberOfHolidays]

Third, something about your errors. The reason is clear. We can use Min or Max to solve this. Because there is only one value there due to filter context. (The Client in Active Projects could be unique or some other field could be).

Best Regards!

Dale

[olimilo]

@v-jiascu-msft

Hi Dale! Thank you for the detailed explanation! I've since updated my data structure to somewhat reflect what you've demonstrated on your post:

I am unsure if this is how it should be (with the relationship directions and the dimensions/facts (do let me know if this needs to be corrected).

I tried this DAX code as a measure and was getting a The operation was cancelled because of locking conflicts error. If I don't catch that error, I just end up with a vis that seems to be eternally loading. Do you know what could the cause be?

Days Elapsed (Window) = 
	VAR DaysElapsed = 
		CALCULATE(
			COUNT('Dates'[Date]),
			FILTER(
				ALL('Dates'),
				'Dates'[Date] > MIN('Active Projects'[CompleteByEndDate]) &&
				'Dates'[Date] <= TODAY() &&
				(
					NOT WEEKDAY('Dates'[Date], 1)
						IN {
							VALUE(LEFT(MIN(Weekdays[WeekendNum]), 1)),
							VALUE(RIGHT(MIN(Weekdays[WeekendNum]), 1))
						}
				)
			)
		)

	RETURN
		SWITCH(
			TRUE(),
			MIN('Active Projects'[CompleteByEndDate]) < TODAY(), DaysElapsed + 0,
			MIN('Active Projects'[CompleteByEndDate]) > TODAY(), -1 * DaysElapsed + 0
		)

[v-jiascu-msft]

@olimilo

Hi,

It’s very strange. I can use this formula without any modification. It seems the error message happened in the database. Do you use a DirectQuery mode with a database? Did the error show when you finished input or used in the report?https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ec842da4-2cfa-4f2b-95e3-8627ba18f622/the-op...

Best Regards!

Dale

[olimilo]

That's the first page I went to when I went looking for the cause of the error. I'm not directly querying from the database. I'm using an Excel spreadsheet for all my tables. The Dates table has at least 5000 rows x 10 rows while the Active Projects table has on average 1500 rows x ~35 columns. Not sure if that contributes to the error though.

I did notice the slowdown when selecting entries from China (the one where we have the most data from). I'm getting values if I use it as a column instead though (although I'm getting an extra "day" in the count, eg: from 5/12 to 5/29 Israel - Fri/Sat weekends, the Days Elapsed should be 12 days, but I'm getting 14.

Days Elapsed (Window) = 
	VAR DaysElapsed = 
		CALCULATE(
			COUNTROWS('Dates'),
			FILTER(
				ALL('Dates'),
				'Dates'[Date] > 'Active Projects'[CompleteByEndDate] &&
				'Dates'[Date] <= TODAY() &&
				(
					NOT WEEKDAY('Dates'[Date], 1)
						IN {
							VALUE(LEFT(MIN(Weekdays[WeekendNum]), 1)),
							VALUE(RIGHT(MIN(Weekdays[WeekendNum]), 1))
						}
				)
			)
		)

	RETURN DaysElapsed + 0

[v-jiascu-msft]

@olimilo

Hi, 

It works well with my table. The dates of >5/12 to <=5/29 are 17 days. The days of saturday is 3. 14 days may be 17 minus 3. So the issue may be from this formula. Only RIGHT part worked. Please check your table WEEKDAYS. 

Israel   Fri,Sat   6,7   Make sure the red part is just three chars without any spaces or any special characters.

{ 
      VALUE(LEFT(MIN(Weekdays[WeekendNum]), 1)), 
      VALUE(RIGHT(MIN(Weekdays[WeekendNum]), 1)) 
}

Best Regards!

Dale