The smallest date of today or next greater than today

[Applicable88]

Hello,

I need the today or next available smallest day than today. I saw solutions in DAX which for every row context can look up into a filtered table 

to get all assignment marked which are of the next working day and today: 

if(isblank(table[column B]),(minx(filter(table,table[column B] >Today()),table[column B])),table[column A])

I would like to do the same already in PowerQuery M. Which I'm not sure how he can look up into a similar approach of a filtered table, to get the smallest date greater than today and today. 

The sample facttable has an assignement No. and Date. There is no assignments on bank holiday and weekends. 

For example today is Friday (21.10.2022) than check for assignment today and next day were company is working.

Sample table:

AssignmentNO		Date		AssignmentCheck
1234		18.10.2022		inactive
1235		19.10.2022		inactive
1236		20.10.2022		inactive
1237		21.10.2022		active
1238		24.10.2022		active
1239		25.10.2022		inactive
1240		25.10.2022		inactive

Basically: Mark all assigments of today or next day. 

How to get a calculated columnd like this in PowerQuery?

Thank you very much in advance.

Best. 

[jbwtp]

Hi @Applicable88,

Are you wanting something similar to this?

Solved: Re: How to add number of days with a static hour v... - Microsoft Power BI Community

Kind regards,

John

[jbwtp · Accepted solution]

Adapted to your scenario:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNlHSUVIAYkMLPUMDPSMDIyMgJzMvMbkksyxVKVYHrMgUpsgSjyIzqCIjAzyKzGGKDJEUoSqxgCkxwanEEqbEFLdVJgb4FcUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AssignmentNO = _t, #" " = _t, Date = _t, AssignmentCheck = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"AssignmentNO", Int64.Type}, {" ", type text}, {"Date", type date},{"AssignmentCheck", type text}}),
    holidays = {
   #date(2023, 1, 1), 
   #date(2023, 1, 2), 
   #date(2022, 9, 2), 
   #date(2022, 9, 4)
},
    today = #date(2022, 10, 21),
    #"Today+NWD" = {today , let 
      hdAdj = (d as datetime)=> 
                  if List.Contains(holidays, Date.From(d)) 
                     or Number.IntegerDivide(Date.DayOfWeek(d),5) > 0 
                     or Time.Hour(d) > 8 
                  then @hdAdj(Date.StartOfDay(d) + #duration (1,0,0,0)) 
                  else d,
      res = hdAdj(DateTime.From(today)+ #duration (1,0,0,0))
   in Date.From(res)},
    Custom1 = #"Changed Type",
    #"Added Custom" = Table.AddColumn(Custom1, "Calculated", each if List.Contains(#"Today+NWD", [Date]) then "active" else "inactive", type text)
in
    #"Added Custom"