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,

 

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's avatar
olimilo
Post Prodigy
9 years ago

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'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