Forum Discussion

Applicable88's avatar
Applicable88
Impactful Individual
3 years ago
Solved

How to get the next available date from today without hardcoding it in M PowerQuery

Hello,

I have a if-statement for calculating me a flag kind of column. It filters me the active and not active assignent ID's. 

From Monday to Thursday I have no problem.  I always look for the assignents with a Finishdate of today or the next day:

 

if ([Finishdate] < Date.From(DateTime.LocalNow()) or [Finishdate] <= Date.AddDays(Date.From(DateTime.FixedLocalNow()), 1)) and [Status] = "Printed" then "Active" else "Inactive"

 

As you can see AddDays functions is hardcoded. When there is a bank holiday or weekend, the formula shouldn't just look for the next date entry which is of course empty. If today is a bank holiday or maybe a Friday, it should look today and for the next available Finishdate entry which is greater than today. 

When today is Friday look for finishdate of today and Monday, if Monday not available than look for Tuesday and so fourth. 

 

Thank you very much in advance.

Best. 

 

 

 

 

Need Help
Show and Tell
Tips and Tricks
  • jbwtp's avatar
    jbwtp
    3 years ago

    Hi Applicable88,

     

    you can test it like this:

     

     

    let 
    dateToCheck = #date(2022, 12, 30), 
    holidays = {#date(2023, 1, 1), #date(2023, 1, 2)},
    nextWorkingDay = (d as date)=> if Number.IntegerDivide(Date.DayOfWeek(d), 5) = 1 /*weekend*/ or List.Contains(holidays, d) /*holiday*/ then @nextWorkingDay(Date.AddDays(d, 1)) /* check the following day */ else d,
    output = nextWorkingDay(Date.AddDays(dateToCheck,1))

in output

     

     

     

    The function nextWorkingDay tests the one that you pass to it and skip until the next working date based on weekends and holiday calendar (which you need to create).

     

    BTW, in your code the first test 

    [Finishdate] < Date.From(DateTime.LocalNow())

    is redundant as if it is satisfied the second test will always satisfy. With OR you can safely ommit it.

     

    Cheers,

    John

     

1 Reply

  • jbwtp's avatar
    jbwtp
    Memorable Member
    3 years ago

    Hi Applicable88,

     

    you can test it like this:

     

     

    let 
    dateToCheck = #date(2022, 12, 30), 
    holidays = {#date(2023, 1, 1), #date(2023, 1, 2)},
    nextWorkingDay = (d as date)=> if Number.IntegerDivide(Date.DayOfWeek(d), 5) = 1 /*weekend*/ or List.Contains(holidays, d) /*holiday*/ then @nextWorkingDay(Date.AddDays(d, 1)) /* check the following day */ else d,
    output = nextWorkingDay(Date.AddDays(dateToCheck,1))

in output

     

     

     

    The function nextWorkingDay tests the one that you pass to it and skip until the next working date based on weekends and holiday calendar (which you need to create).

     

    BTW, in your code the first test 

    [Finishdate] < Date.From(DateTime.LocalNow())

    is redundant as if it is satisfied the second test will always satisfy. With OR you can safely ommit it.

     

    Cheers,

    John