Date Calculation with validation

[CJ_96601]

Good afternoon, 

Please find below sample table as well as what's required.

Need to have this in advance editor

Date1	Date2 (assuming current date is May 20, 2021)	# of Days
15-May-21		6
1-Feb-20	5-Feb-20	4
1-Jul-20	30-Jun-21	324
if date 2 is null then current date minus date 1,
else if date 2 is greater than current date then current date - date 1
else date 2 - date 1

I appreciate if somenone could provide a solution

[Fowmy · Accepted solution]

@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"

[Anonymous · Accepted solution]

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

[Fowmy · Accepted solution]

@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"

[CJ_96601]

Thanks..

[CJ_96601]

Thanks, i have the same code but it's giving me an error on null values

[CJ_96601]

Thanks, i have the same code but it's giving me an error.

[Fowmy]

What is the error? 
Please share the screenshot of the error.