Forum Discussion
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
- amitchandakSuper User
Applicable88 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
- Applicable88Impactful Individual
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.
- Applicable88Impactful Individual
- Applicable88Impactful Individual
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
- Applicable88Impactful Individual
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.