Define next date and previous date power query

[fbittencourt]

Hi all, I need to develop a condition( ( 2 diff columns) for the next date from a column  and previous date separated as the example below:

If the date of the column data photo clarity is higher than today show the next date from the column

EX:  if Today is 20/11/2024  then the new column will show 07/12/2024 ( Next date)

       if  Today is 20/11/2024 the new column will show 02/11/2024 ( previous date)

Tks in advance!!

[Anonymous · Accepted solution]

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.

[Bitt20204]

try this

let
Source = Excel.Workbook(File.Contents("C:\Users\ferna\OneDrive\Documentos\Data photo clarity.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"num_photo", Int64.Type}, {"Date photo clarity", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Today's date", each DateTime.Date(DateTime.LocalNow()),

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [num_photo = _t, #"Date photo clarity" = _t, Année = _t, Mois = _t, Index = _t, #"Today's date" = _t]),
InsertedDateSubtraction = Table.AddColumn(#"Added Custom", "Subtraction", each Duration.Days([Date photo clarity] - [#"Today's date"]), Int64.Type),
CalculatedMax = List.Max(List.Transform(InsertedDateSubtraction[Subtraction],(a)=>if a <0 then a else null)),
CalculatedMin = List.Min(List.Transform(InsertedDateSubtraction[Subtraction],(a)=>if a >0 then a else null)),
AddedCustom = Table.AddColumn(InsertedDateSubtraction, "Nearest Before", each if [Subtraction] = CalculatedMax then [Date photo clarity] else null,type date),
AddedCustom1 = Table.AddColumn(AddedCustom, "Nearest After", each if [Subtraction]=CalculatedMin then [Date photo clarity] else null,type date),
RemovedOtherColumns = Table.SelectColumns(AddedCustom1,{"num_photo", "Date photo clarity", "Index", "Nearest Before", "Nearest After"}),
#"Added Custom1" = Table.AddColumn(RemovedOtherColumns, "Today's date", each DateTime.Date(DateTime.LocalNow())),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"num_photo", "Date photo clarity", "Index", "Today's date", "Nearest Before", "Nearest After"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Today's date", type date}, {"Date photo clarity", type text}, {"Nearest Before", type text}, {"Nearest After", type text}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Today's date", type text}})
in
#"Changed Type2"

[Anonymous · Accepted solution]

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.

[fbittencourt]

Thanks fou your response , but I have a little issue, could you check where is my error?

First step: 

2 step: my code:

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]),
#"Added Custom" = Table.AddColumn(#"En-têtes promus", "Custom", each let a=[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}),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Date photo clarity", type date}})
in
#"Changed Type"

3 step the error

[Bitt20204]

Try this:

Source = Excel.Workbook(File.Contents("C:\Users\ferna\OneDrive\Documentos\Data photo clarity.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"num_photo", Int64.Type}, {"Date photo clarity", type date}}),
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Today's date", each DateTime.Date(DateTime.LocalNow()),

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [num_photo = _t, #"Date photo clarity" = _t, Année = _t, Mois = _t, Index = _t, #"Today's date" = _t]),
InsertedDateSubtraction = Table.AddColumn(#"Added Custom", "Subtraction", each Duration.Days([Date photo clarity] - [#"Today's date"]), Int64.Type),
CalculatedMax = List.Max(List.Transform(InsertedDateSubtraction[Subtraction],(a)=>if a <0 then a else null)),
CalculatedMin = List.Min(List.Transform(InsertedDateSubtraction[Subtraction],(a)=>if a >0 then a else null)),
AddedCustom = Table.AddColumn(InsertedDateSubtraction, "Nearest Before", each if [Subtraction] = CalculatedMax then [Date photo clarity] else null,type date),
AddedCustom1 = Table.AddColumn(AddedCustom, "Nearest After", each if [Subtraction]=CalculatedMin then [Date photo clarity] else null,type date),
RemovedOtherColumns = Table.SelectColumns(AddedCustom1,{"num_photo", "Date photo clarity", "Index", "Nearest Before", "Nearest After"}),
#"Added Custom1" = Table.AddColumn(RemovedOtherColumns, "Today's date", each DateTime.Date(DateTime.LocalNow())),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"num_photo", "Date photo clarity", "Index", "Today's date", "Nearest Before", "Nearest After"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Today's date", type date}, {"Date photo clarity", type text}, {"Nearest Before", type text}, {"Nearest After", type text}}),
#"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"Today's date", type text}})
in
#"Changed Type2"

[Anonymous · Accepted solution]

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.

[fbittencourt]

Thank you for your response, it works now, sorry to bother you, I will accept the solutio, if you have time, how can I split that calculation to retrieve the same information, but in two different columns , Previous date and next date, instead the if condition the we have now. Congrats for your work!! tks again!

[ronrsnfld]

You can easily modify my solution to return two columns:

let
    Source = theTable,

//next two steps may not be needed if dates are already sorted
// and also if num_photo could be used as an Index
    #"Sorted Rows" = Table.Sort(Source,{{"Date photo clarity", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    
    #"Added Teste" = Table.AddColumn(#"Added Index", "Teste 1",
        each if [Index] = 0 then null 
            else if [Date photo clarity] < Date.From(DateTimeZone.FixedLocalNow())
                then [Previous Date = #"Added Index"[Date photo clarity]{[Index]-1}, Next Date = null]
                else [Previous Date=null, Next Date= #"Added Index"[Date photo clarity]{[Index]+1}?],
                type [Previous Date = nullable date, Next Date=nullable date]),
    
    
    #"Removed Columns" = Table.RemoveColumns(#"Added Teste",{"Index"}),
    #"Expanded Teste 1" = Table.ExpandRecordColumn(#"Removed Columns", "Teste 1", {"Previous Date", "Next Date"})
in
    #"Expanded Teste 1"

[Omid_Motamedise]

Next Day= DateTime.LocalNow()+Duration.From(1)

Previous  Day= DateTime.LocalNow()+Duration.From(-1)

[PhilipTreacy]

@fbittencourt 

It's not clear what you are asking.  I can't see how the example you've given and the image of data you provided are connected. I don't see any date 20/11/2024 in the photo.

Please restate the problem and give an example that is related to sample data that you provide - please provide a file or table of data not an image.

Regards

Phil

[fbittencourt]

Again more simple:

Ex:  1- Today is 20/11/24 -

       I need to show the first previous date from the column DAte photo Clarity in one column ( 02/11/2024)

       and

      I need to show the first next date from the column Date photo Clarity in one column ( 07/12/2024)

[ronrsnfld]

Paste the below code into your Advanced Editor to see one way of doing things.

Then replace the lines where I create a sample table (Lines Source and above) with your own data.

let

//create example table
// replace below lines with your own source data
        Days = Table.TransformColumnTypes(
        Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlOK1YlWMgaTRmASImICJg2RRCBqzMGkKZJ6cwz1hkhqcOnCakssAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t]),
            {"Day", Int64.Type}),
        Months = List.Combine({{1..12},{1..12}}),
        Year = List.Combine({List.Repeat({2024},12), List.Repeat({2025},12)}),        
        YMD = List.Transform(List.Zip({Year,Months,Days[Day]}), each #date(_{0},_{1},_{2})),        
    Source = Table.FromColumns(
        {{1..24}}
        & {YMD}
        & {Year}
        & {Months},
        type table[num_photo=Int64.Type, Date photo clarity=date, Année=Int64.Type, Mois=Int64.Type]),

//next two steps may not be needed if dates are already sorted
// and also if num_photo could be used as an Index
    #"Sorted Rows" = Table.Sort(Source,{{"Date photo clarity", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    
    #"Added Teste" = Table.AddColumn(#"Added Index", "Teste 1",
        each if [Index] = 0 then null 
            else if [Date photo clarity] < Date.From(DateTimeZone.FixedLocalNow())
                then #"Added Index"[Date photo clarity]{[Index]-1}?
                else #"Added Index"[Date photo clarity]{[Index]+1}?, type nullable date),
    #"Removed Columns" = Table.RemoveColumns(#"Added Teste",{"Index"})
in #"Removed Columns"

Results from your posted date

Unlike your posted data, note that the first and last rows cannot be calcualted.