How to check if Date A is X days before Date B?

[stan255]

I have two date columns - Date A and Date B.

How can I check if Date A is within 7 days before any of Date B?

[Omid_Motamedise · Accepted solution]

Copy the below code and paste it into the advance editor.

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9LDoAgDETv0rUJ7YCCZ2m4/zXEFZ2auHzJfN3FatFRoGgyDxdchIvuTbawbxwktVZMA3a2DrKCWw08wj7iGFWpKC1+vUGMzlEnYVrFh6r+PkjBYHE4NB8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable date) meta [Serialized.Text = true]) in type table [Date = _t]),
    TableAChangedType = Table.TransformColumnTypes(TableA,{{"Date", type date}}),
    TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3sNQ3MjAyUYrViVYyNEbhmqHwTPQNDRA8CxQ5I6A5FriUGhugqDVHkTSyROiMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable date) meta [Serialized.Text = true]) in type table [Date = _t]),
    TableBChangedType = Table.TransformColumnTypes(TableB,{{"Date", type date}}),
    Custom1 = Table.AddColumn(TableAChangedType, "Flag", each not Table.IsEmpty(Table.SelectRows(TableBChangedType, (x)=> x[Date]>=[Date] and Date.AddDays(x[Date],-7)<=[Date])))
in
    Custom1

I have added a new column into Table A with true value for the rows which are with in the 7 days of at least a date in Table B

[Omid_Motamedise · Accepted solution]

Copy the below code and paste it into the advance editor.

let
    TableA = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fY9LDoAgDETv0rUJ7YCCZ2m4/zXEFZ2auHzJfN3FatFRoGgyDxdchIvuTbawbxwktVZMA3a2DrKCWw08wj7iGFWpKC1+vUGMzlEnYVrFh6r+PkjBYHE4NB8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable date) meta [Serialized.Text = true]) in type table [Date = _t]),
    TableAChangedType = Table.TransformColumnTypes(TableA,{{"Date", type date}}),
    TableB = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtc3sNQ3MjAyUYrViVYyNEbhmqHwTPQNDRA8CxQ5I6A5FriUGhugqDVHkTSyROiMBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable date) meta [Serialized.Text = true]) in type table [Date = _t]),
    TableBChangedType = Table.TransformColumnTypes(TableB,{{"Date", type date}}),
    Custom1 = Table.AddColumn(TableAChangedType, "Flag", each not Table.IsEmpty(Table.SelectRows(TableBChangedType, (x)=> x[Date]>=[Date] and Date.AddDays(x[Date],-7)<=[Date])))
in
    Custom1

I have added a new column into Table A with true value for the rows which are with in the 7 days of at least a date in Table B

[jgeddes]

Something like this might work for you...

[stan255]

Thanks for this solution. I'm getting this error

"DataFormat.Error: We couldn't convert to Date.
Details:
[List]"

[v-xinruzhu-msft · Accepted solution]

Hi,

Thanks for the solutions @Omid_Motamedise  and @jgeddes  offered, and i want to offer some more information for user to refer to.

hello @stan255 , baded on your picture, your two date columns come from different tables, in table A , you can create a cusotm column.

let a=Query2[reportedDate],
b=[Open],
c=List.Count(List.Select(a,each b>=Date.AddDays(_,-7) and b<=_))
in if c>0 then "Y" else "N"

Output

You can refer to the attachment.

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.