Return if its the NEXT production workday

[Applicable88]

Hello, 

I have following problem. When the 1st shift is finished after around 15:00 o'clock , we already doing the orders of next day.Before that the visualisation will not consider them, only the orders older or equal today.  So my m-query is stating with a statement in simple terms (Please disregard the DatetimeZone.Switchzone function, its just because the powerbi server uses UTC time:

if Time.Hour(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),2,0))>=15 
and 
Time.Hour(DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),2,0))<=23 
(if [OutbounDate]<=Date.From(DateTime.LocalNow()) or [OutbounDate]=Date.AddDays(Date.From(DateTime.FixedLocalNow()), 1)) then "To Do" else "Backlog"

So from 3PM on until 11PM orders from older than today and also tomorrow are considered. 

The problem with this lies that the AddDays function is fixed. But what when tomorrow is public holidays or vacation a several day and the company is closed? Then we would consider the orders which have a outbounddate of the first working day after the vacation, public holidays or even weekend. 

I just found a Mastercalendar with "ProductiveWorkdays" as a flag. This shows me which weekdays are actually working days=1 or 

off days=0.

In this example for example you see that Monday and Tuesday with the red 0 are off days. And if today would be the Friday before the long weekend, how to tell the formula to return only the ordernumber from of the very first day with ProductiveWorkday =1? 

But only the very first one, so in this example it would be only the order with the date 19.05.2021, the Wednesday.

OutboundDate	DayName	ProductiveWorkday	Ordernumber
11.05.2021	Tuesday	1	1234
12.05.2021	Wednesday	1	1235
13.05.2021	Thursday	1	1236
14.05.2021	Friday	1	1237
15.05.2021	Saturaday	0	1238
16.05.2021	Sunday	0	1239
17.05.2021	Monday	0	null
18.05.2021	Tuesday	0	null
19.05.2021	Wednesday	1	1242
20.05.2021	Thursday	1	1243
21.05.2021	Friday	1	1244
22.05.2021	Saturaday	0	1245
23.05.2021	Sunday	0	1246
24.05.2021	Monday	1	1247
25.05.2021	Tuesday	1	1248
26.05.2021	Wednesday	1	1249

Thank you in advance.

Best.

[watkinnc · Accepted solution]

The key is to obtain the date of the next legitimate workday. I would add a blank query to use as a parameter:

NextDay = List.Min(Table.SelectRows(TableName, each [ProductiveWorkday] = 1 and [OutBoundDate] > Date.From(DateTime.LocalNow()))[OutBoundDate])

This will give you the first ProductiveWorkday Date after today. 
Now you can add a step to your MainTable:

if Time.Hour(DateTime.LocalNow())>= 15 and Time.Hour(DateTime.LocalNow())<= 23 then Table.SelectRows(MainTable, each [OutBoundDate] >= Date.From(DateTime.LocalNow()) or [OutBoundDate] = NextDay) else TableSelectRows(MainTable, each OutBoundDate <= Date.From(DateTime.LocalNow()))

I would also make a Today query (=DateTime.LocalNow()) so that I didn't have to keep typing Date.From(DateTime.LocalNow()), just type Today instead.

--Nate

[Applicable88]

Wow @watkinnc , I think that should be the solution :). One question: Are you referring to the same table when you wrote "TableName" on the top query and "MainTable" in the second query?

Thank you very much.

Best

[watkinnc]

Yes, sorry for the inconsistency. I'm usually on my iPhone here 🙂

[Applicable88]

@watkinnc 😊, thank you very much!

[ImkeF]

Hi @Applicable88 ,

if my understanding is correct, the following should work:
- Add a column where you check if "ProductiveWorkday" is null

- if so, return null, else return "Ordernumber"

- check new column and "Fill up"

[Applicable88]

Hello @ImkeF ,

thanks for the reply. But thats not what I want to return. I might explained too complicated.Your example would return every productive working day. But I want from todays standpoint ONLY the ordernumber returned from the next outbounddate which is also a productive working day =1 . 

According to the table above, if today would be Tuesday May 11th, it would return me all ordernumbers with a outbound date until today AND of tomorrow. If its Wednesday May 12th, it should do the same including the next day of May 12th which is May 13th. So its always the ordernumbers older or today and of the next day. For Friday I have a exception build in for a offeset of three days because of the weekend.

As you can see my function is very rigid and only consider next day or 3 days for weekends offset.

So now comes the problem. What if the next day or the Monday after the weekend is not a productive working day (indicated as 0)? 

How can I utilize a dynamic if statement which is saying:

if orderoutbounddate is older or equal today and also the ordernumber of just the VERY NEXT orderoutbounddate which is a productive working day. 

[ImkeF]

Hi @Applicable88 ,

sorry, but this is too complicated for me to understand without a sample table with specified desired results.

It might be a good start to create some intermediate columns as well that display results of the various conditions.

That would make troubleshooting much easier and also help other helpers in this forum who might want to pick this up.

[Applicable88]

Hello @ImkeF,

I try to simplified my problem and also provided a excel with above data and the pbix file where I already did the calculated column which only lacks one function that I hope to find.

https://drive.google.com/drive/folders/1Jox5PfTJCfBP9x9hnQ6c8UBch_h7YIBq?usp=sharing

Given today is already May 9th, means Sunday, the next day would be a productive workday. 

According to this calculated column with this if statement:

if ([OutboundDate]=Date.From(DateTime.LocalNow()) or [OutboundDate]=Date.AddDays(Date.From (DateTime.FixedLocalNow()), 1)) and [ProductiveWorkday]=1 then "Orders" else "Fail"

I get following outcome:

Thats because tomorrow is the next day and also a productive day. But my function is rigid, since it can only lookup for the next day. But what if there are off days/weekends/ public holidays between today and the next working day?

If tomorrow would be a public holiday or vacation, my function wouldn't work. As you can see for example Monday the 17th and 18th of May are both public holiday indicated with 0 as not productive.  If thats the case it should return the first day after weekend/public holiday/ vacation which is again productive =1. In that case it would be wednesday 19th. 

Example is here: 

Given today is Friday May 14th return today and the next working day which is May 19th with "Orders" instead of "Fail" .Since all days in between are non productive days. 

I hope I explained better.