Get the first date just before today´s date and first date after today´s date

[fbittencourt]

Hi folks,

I need two new columns based on the today´s date.

1- One column to retrieve the first date before today´s date from the column Date Photo Clarity

2- Another column to retrieve the first date after today´s date from the column Date Photo Clarity

Ex:   Today is 25/11    Previous date  will be 02/11/2024  and next date will be 07/12/2024

Tks all

[p45cal · Accepted solution]

try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZNbjsMgEATv4u9I0MPTZ7Fy/2usBzqruDNflnGVDBRc14HjdeSeMpJlq/cLH2vcX1sCv71f12E+XFI2wY2K4MVxS7fxxAsVwSsnUwX/V554c7ym3ARvVATvjiPdP3jinYrgg5MZgg8qgk/uzBR8UhH8dHykfAp+UhEc3uJeKLJ2ypRUwN56/ITFx1LD9pSgbdcHBHVRGGCNtW00ngQEfVGZwERYfwgKo1EoIjiLoDH63qd9hL6EtZCgMga7NRHWOoLOmCzXRXAWQWmcnNIQwVkErS3z1kwRnEXQ2j6X+BRhsdEtth1un6bvcAsOUlvZIfCTesFBa+NlhrbeB0Njv/8A", BinaryEncoding.Base64), Compression.Deflate)), 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]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date photo clarity", type date}, {"Today's date", type date}}),
    InsertedDateSubtraction = Table.AddColumn(ChangedType, "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"})
in
    RemovedOtherColumns

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @Omid_Motamedise , @p45cal  and @bhanu_gautam  offered, and i want to offer some more information for user to refer to.

hello @fbittencourt , you have a similar thread

Solved: Re: Define next date and previous date power query - Microsoft Fabric Community

You can try the following code.

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 with Locale" = Table.TransformColumnTypes(Source, {{"Date photo clarity", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"num_photo", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "PreviousDate", each let a=[num_photo],
b=try Table.SelectRows(#"Changed Type",each [num_photo]=a-1)[Date photo clarity]{0} otherwise null,
c=if [Date photo clarity]<Date.From(DateTime.LocalNow()) then b else null 
in c),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "NextDate", each let a=[num_photo],
b=try Table.SelectRows(#"Changed Type",each [num_photo]=a+1)[Date photo clarity]{0} otherwise null,
c=if [Date photo clarity]>Date.From(DateTime.LocalNow()) then b else null 
in c)
in
    #"Added Custom1"

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.

[Omid_Motamedise]

Use the below pattern

Table.Min(

Table.selectrows(Source, each _[Date photo clarity]>Date.From(DateTime.LocalNow())), each [Date photo clarity])

[p45cal · Accepted solution]

try:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZNbjsMgEATv4u9I0MPTZ7Fy/2usBzqruDNflnGVDBRc14HjdeSeMpJlq/cLH2vcX1sCv71f12E+XFI2wY2K4MVxS7fxxAsVwSsnUwX/V554c7ym3ARvVATvjiPdP3jinYrgg5MZgg8qgk/uzBR8UhH8dHykfAp+UhEc3uJeKLJ2ypRUwN56/ITFx1LD9pSgbdcHBHVRGGCNtW00ngQEfVGZwERYfwgKo1EoIjiLoDH63qd9hL6EtZCgMga7NRHWOoLOmCzXRXAWQWmcnNIQwVkErS3z1kwRnEXQ2j6X+BRhsdEtth1un6bvcAsOUlvZIfCTesFBa+NlhrbeB0Njv/8A", BinaryEncoding.Base64), Compression.Deflate)), 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]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Date photo clarity", type date}, {"Today's date", type date}}),
    InsertedDateSubtraction = Table.AddColumn(ChangedType, "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"})
in
    RemovedOtherColumns

[fbittencourt]

Thanks for the answer I had a small issue on my code:

let

Source = Excel.Workbook(File.Contents("C:\Users\h22012\Pilotage Perf Codir et Groupe\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 Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Today", 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]),
ChangedType = Table.TransformColumnTypes(Source,{{"Date photo clarity", type date}, {"Today's date", type date}}),
InsertedDateSubtraction = Table.AddColumn(ChangedType, "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"})
in
RemovedOtherColumns

[p45cal]

You will have to check that "Date photo clarity" is the exact name of your column. PQ is case sensitive so check that that is correct (I could only go from your picture) and that there aren't any leading or trailing spaces.

You could filter that column in the user-interface then look at the code and see if the column name is the same.

[bhanu_gautam]

@fbittencourt , Try using below steps

Open Power Query Editor in Power BI.
Add a new column for the previous date.
Add a new column for the next date.
Here is the M code to achieve this:

let
// Get today's date
Today = DateTime.Date(DateTime.LocalNow()),

// Add a column for the previous date
AddPreviousDateColumn = Table.AddColumn(
YourTableName,
"Previous Date",
each
let
FilteredRows = Table.SelectRows(YourTableName, each [Date Photo Clarity] < Today),
SortedRows = Table.Sort(FilteredRows, {{"Date Photo Clarity", Order.Descending}})
in
if Table.RowCount(SortedRows) > 0 then SortedRows{0}[Date Photo Clarity] else null
),

// Add a column for the next date
AddNextDateColumn = Table.AddColumn(
AddPreviousDateColumn,
"Next Date",
each
let
FilteredRows = Table.SelectRows(YourTableName, each [Date Photo Clarity] > Today),
SortedRows = Table.Sort(FilteredRows, {{"Date Photo Clarity", Order.Ascending}})
in
if Table.RowCount(SortedRows) > 0 then SortedRows{0}[Date Photo Clarity] else null
)
in
AddNextDateColumn

Replace YourTableName with the actual name of your table. This code will add two new columns to your table: "Previous Date" and "Next Date," which will contain the first date before today's date and the first date after today's date, respectively.