topic Re: True False statement business hours vs outside business hours incl. national holidays in DAX Commands and Tips

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

Dave1972 — Fri, 16 Oct 2020 07:43:26 GMT

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:

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

Delete

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

Ashish_Mathur — Fri, 16 Oct 2020 12:00:22 GMT

Hi,

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

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

Anonymous — Fri, 16 Oct 2020 12:07:09 GMT

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

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

Dave1972 — Fri, 16 Oct 2020 15:43:23 GMT

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.

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

Ashish_Mathur — Sat, 17 Oct 2020 01:21:22 GMT

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.

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

Dave1972 — Sun, 18 Oct 2020 19:15:25 GMT

Hi Ashish,

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

Thx D

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

Ashish_Mathur — Wed, 21 Oct 2020 11:47:57 GMT

Hi,

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

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

Dave1972 — Thu, 22 Oct 2020 12:04:21 GMT

Hi Ashish,

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

Thanks again for assistence.

Dave