Forum Discussion

olimilo's avatar
olimilo
Post Prodigy
9 years ago

NETWORKDAYS.INTL with Country as context

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 Jorda...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
9 years ago

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-operation-was-cancelled-because-of-locking-conflicts?forum=sqlanalysisservices

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

olimilo's avatar
olimilo
Post Prodigy
9 years ago

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's avatar
    v-jiascu-msft
    Microsoft Employee
    9 years ago

    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