Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
5 years ago
Solved

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

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.  

  • 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. 

5 Replies