Define next date and previous date power query

Hi,

Thanks for the solution ronrsnfld  and Omid_Motamedise offered, and i want to offer some more infotmaiton for user to refer to.

hello fbittencourt , you cna create a blank query and input the following code to advanced editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc7BDcAgCIXhXTib4ENQO4vp/muU4gGuX4CfcwjUaDJYuii97ZA4DJaEEROaoA7KlmAO4JkwY2UlrDi6E7aDMEr3cVmMEkaPUMzYFURJikjIKPL/azfu8n4=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [num_photo = _t, #"Date photo clarity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"num_photo", Int64.Type}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Date photo clarity", type date}}, "en-GB"),
    #"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let a=#"Changed Type with Locale"[Date photo clarity],
//#"Changed Type with Locale" is the last step name,replace it with your last step name
b=Date.From(DateTime.LocalNow()),
c=[num_photo]-1,
d=List.Generate(
    ()=>[x=1,y=if a{0}<b then null else a{1}],
    each [x]<=List.Count(a),
    each [y=try if a{[x]}<b then a{[x]-1} else a{[x]+1} otherwise null,x=[x]+1],
    each [y])
    in d{c})
in
    #"Added Custom"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi fbittencourt 

Please try the the following code.

let Source = Excel.Workbook(File.Contents("C:\Users\h22012\OneDrive -88-Power BI\Dev DataSource\Autres\REF_Date-Photo-Clarity.xlsx"), null, true),
Feuil1_Sheet = Source{[Item="Feuil1",Kind="Sheet"]}[Data],
#"En-têtes promus" = Table.PromoteHeaders(Feuil1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"En-têtes promus",{{"Date photo clarity", type date},{"num_photo", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 
let a=#"Changed Type"[Date photo clarity],
b=Date.From(DateTime.LocalNow()),
c=[num_photo]-1,
d=List.Generate(
()=>[x=1,y=if a{0}<b then null else a{1}],
each [x]<=List.Count(a),
each [y=try if a{[x]}<b then a{[x]-1} else a{[x]+1} otherwise null,x=[x]+1],
each [y])
in d{c}),

in
#"Added Custom"

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.