Date Calculation with validation

CJ_96601 

Paste below code in the Avdanced Editor and check the Add Column step:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTV9U2s1DUyVNJRUorViVYyMNR1S03SNTIAChiYwthQGa/SHIiMsQGQnQfSFhsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each  if [Date2]=null then Date.From(DateTime.LocalNow()) - [Date1] else if [Date2] > Date.From(DateTime.LocalNow()) then Date.From(DateTime.LocalNow()) - [Date1] else [Date2]-[Date1]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}})
in
    #"Changed Type1"

Hi CJ_96601,

You can also try to create a calculated column for calculation the datediff of the two date fields:

Diff =
DATEDIFF (
    [Date1],
    IF ( MIN ( [Date2], TODAY () ) <> BLANK (), [Date2], TODAY () ),
    DAY
)

Regards,

Xiaoxin Sheng