Date function in nested If-statement, but changing after certain time

[Applicable88]

Hello,

I tried many ways to calculate  a dynamic time problem, but the calculated column in M language giving me trouble.

I use following if statement:

if ([deliverydate]<=Date.From(DateTime.LocalNow())or [deliverydate]=Date.AddDays(Date.From(DateTime.FixedLocalNow()), 1)) 
and ([Code]="Z" or [Status]="Y") and [Way]="TRE"and [CCode]<>"USA" then "Daily" else "Fail"

Ordernumber with a delivery of today or older  or orders of the next day  got returned "Daily" otherwise its a "Fail". 

The problem for example, that we are working all around the clock: From morning until 3PM there is only ordernumbers with today's or older delivery orders in the backlog which needs to be taken care of. At 3PM there is a reset and all the finished deliveries got deleted, but of course some are still not finished. So now correctly we have some leftover orders which has todays planned deliverydate, but also new orders of tomorrows date in the backlog. So far no problem, the second date function will return these orders. My problem is exactly after midnight, not only todays orders are returned, but also the orders of one day after, which only should be considered after the now next afternoons 3PM time.

Maybe someone already encountered this problem? 

Thank you in advanced.

Best.  

[Applicable88 · Accepted solution]

Found my solution. Defintely add another time function base on the system now() time where it compares what time of the time range it is at automatic dataset refresh.  So that it will use the right nested statement according to the time of today. 

[amitchandak]

@Applicable88 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Applicable88]

Hello, 

maybe someone has a solution?

Sample Pbix File and Excel Sample Data is here. I also explained whats the problem: 

https://drive.google.com/drive/folders/1Iukm83AWa5hXDeqksfgqQoF-65_XNv2C?usp=sharing  

[Applicable88]

@amitchandak, I sent you the link to the sample data in your message inbox. 

Thank you.

Best. 

[Applicable88]

Hello @amitchandak, 

thank you very much, I will provide you a sample data for this. I will send you link in private message. Talk to you later. 

Best.