Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dave1972
Helper I
Helper I

True False statement business hours vs outside business hours incl. national holidays

Hi All,

I have a question about a creation of a True-False statement if a call is coming in during business hours or if it is outside business hours. During business hours is Monday to Friday between 8 AM and 5PM. Saterday + Sundays and national holidays are outside buisness hours.

 

I did manage to calculate the more simple true-false statement on just Business Days:  BusinessDAYS = IF('# Calendar'[WeekdagNr]<6, TRUE())...that works correctly, however if I want to add as 2nd step the working hours element I get error message:

Screenshot 2020-09-23 172424.jpg

 

And I then 3rd step I also need to include the national holidays as outside business days ...

The value that determined if a national holiday are in table 'Nat Holidays NL' where field "Officiele vrije dag" should have value "Yes". The date and time of the connection are in another table called 'pbiTotalen'[Date]  and 'pbiTotalen'[Time]

Appreciate if you can guide me me how to resolve... Thx for helping...D

Schema Table.jpg

 

 

 

 
 
  •  
7 REPLIES 7
Anonymous
Not applicable

If you have a formula that is incorrect syntactically, please use www.daxformatter.com. It'll show you where the problem is.
Ashish_Mathur
Super User
Super User

Hi,

Some problem in the bracketing there.  Before the <TIME, there should be ,1)


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks Ashish. That part is solved, but still interested to understand how to integrate the 3rd piece ...including also the fact that in case national holiday that is dureing weekdays this is regarded also as non working hours, so would like to see complete DAX function.

Hi,

Using the RELATED() or LOOKUPValUE() function, bring over to your Calendar table, the date of the public holiday from the Public Holiday table.  Then modify your IF() formula to check if the Holiday column cell is blank or not.

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

So how would complete DAX statement look like. Incorporating the earlier statement with the last part?

Thx D

Hi,

Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi Ashish,

Send you personally a link to location for downloading PBI file. 

Thanks again for assistence.

Dave

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors