Forum Discussion
Solved
Date Calculation with validation
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
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
6 Replies
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
