Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Applicable88
Impactful Individual
Impactful Individual

The smallest date of today or next greater than today

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. 

 

1 ACCEPTED SOLUTION
jbwtp
Memorable Member
Memorable Member

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"

View solution in original post

2 REPLIES 2
jbwtp
Memorable Member
Memorable Member

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
Memorable Member
Memorable Member

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"

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors