Find the min Date between 4 Date columns ONLY when the date is within 90 days of another

[alya1]

Hi All,

I have table like below:

ID	Date1	Date2	Date3	Date4	Any Within 90 days? (calculuated column)
111		1/1/2020	1/5/2020		yes
222	9/3/2020			1/3/2020	no
333	1/6/2020		5/5/2020	6/5/2020	yes
444	12/12/2020	1/8/2020		2/1/2020	yes
555			1/10/2020		no

I would like to create a new column with the min date only when the date is within 90 days of another like below:

ID	MinDate90Days
111	1/1/2020
222
333	5/5/2020
444	1/8/2020
555

Thank you and Happy Holidays!

[Anonymous]

Hi @alya1,

Thanks the reply from  Ashish_Mathur and ryan_mayu.

To achieve your need, I think Power Query could achieve your need.

1. This is my sample data:

2. Here is what I have in the applied steps, please check for reference:

let
    // Step 1: Load data from a compressed JSON source and convert it to a table
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VY5RCsAwCEOvUvwuWGMt21lK73+N1TLXDhRCeJr0TiJCmdJcYWEUlCUtZKKROwGY+mbddhy9llOquqx2UrZ/tZAO11odBs/5Yq/zElHIcTP7xUo5Go4H", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"ID     " = _t, #"Date1           " = _t, #"Date2        " = _t, #"Date3           " = _t, #"Date4        " = _t]),

    // Step 2: Change the data types of columns for proper handling (ID to integer, dates to date)
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID     ", Int64.Type}, {"Date1           ", type date}, {"Date2        ", type date}, {"Date3           ", type date}, {"Date4        ", type date}}),

    // Step 3: Unpivot all date columns except "ID" to have one column for date labels (Attribute) and one for values (Value)
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID     "}, "Attribute", "Value"),

    // Step 4: Perform a self-join on the "ID" column to match each row with other rows with the same ID
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Columns", {"ID     "}, #"Unpivoted Columns", {"ID     "}, "Unpivoted Columns", JoinKind.LeftOuter),

    // Step 5: Expand the merged query to extract the "Value" from the nested table, renaming it to "Value.1"
    #"Expanded Unpivoted Columns" = Table.ExpandTableColumn(#"Merged Queries", "Unpivoted Columns", {"Value"}, {"Value.1"}),

    // Step 6: Add a custom column to calculate the absolute difference in days between "Value" and "Value.1"
    #"Added Custom" = Table.AddColumn(#"Expanded Unpivoted Columns", "Custom", each Number.Abs(Duration.Days([Value]-[Value.1]))),

    // Step 7: Replace 0s with null in the "Custom" column, as we are only interested in non-zero differences
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom",0,null,Replacer.ReplaceValue,{"Custom"}),

    // Step 8: Filter out rows where the absolute day difference is greater than 90 or is null
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each [Custom] <= 90 and [Custom] <> null),

    // Step 9: Remove duplicate rows based on the "Custom" column
    #"Removed Duplicates" = Table.Distinct(#"Filtered Rows", {"Custom"}),

    // Step 10: Add another custom column that selects the smaller date value between "Value" and "Value.1"
    #"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom.1", each if [Value] < [Value.1] then [Value] else [Value.1]),

    // Step 11: Remove unnecessary columns like "Attribute", "Value", "Value.1", and "Custom" to clean up the result
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute", "Value", "Value.1", "Custom"})
in
    #"Removed Columns"


    

3. Here is the final result:

I hope this information is helpful and if you have any further questions please feel free to contact me.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[ryan_mayu]

@alya1 

you can try this in PQ

= Table.AddColumn(#"Changed Type", "Custom", each
[a= List.Sort(List.RemoveNulls( List.Skip(Record.ToList(_)))),
b=List.Transform(a, each Number.From(_)),
c = List.RemoveFirstN(b, 1),
d = List.Zip({b, c}),
e = List.RemoveNulls( List.Transform(d, each _{1} - _{0})),
f =List.PositionOf(e, List.First(List.Select(e, each _ < 90 ))),
g = if f = -1 then null else b{f},
h = Date.From(g)
]
[h])

pls see the attachment below

[Ashish_Mathur]

Hi,

Would you be OK with a measure based solution?

[alya1]

yes

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.