Forum Discussion
Conditional based on difference between current and previous row from different columns
Dear community, How do I compute the difference (days) between a column and the next row of another column and build a conditional which uses this difference? As it can be seen in the example...
Hello,
Thanks for your comments, yes I realised that. So I added two indexes, one which orders the clients by ID and contract dates, and one which orders the clients by ID only:
| Client ID | Contract Number | Index | Index Contract | Contract Start Date | Contract End Date |
| 50951326532 | 501136 | 1 | 6 | 10/1/2014 0:00 | 9/30/2015 0:00 |
| 50951326532 | 501136 | 2 | 6 | 10/1/2015 0:00 | 12/31/2016 0:00 |
| 50951326532 | 501136 | 3 | 6 | 1/1/2017 0:00 | 9/30/2017 0:00 |
| 50951326532 | 501136 | 4 | 6 | 10/1/2017 0:00 | 12/31/2017 0:00 |
| 50951326532 | 501136 | 5 | 6 | 1/1/2018 0:00 | 12/31/2018 0:00 |
| 50951326532 | 501136 | 6 | 6 | 1/1/2019 0:00 | 2/25/2020 0:00 |
| 50952154702 | 880944 | 7 | 8 | 10/1/2014 0:00 | 9/30/2015 0:00 |
| 50952154702 | 880944 | 8 | 8 | 10/1/2015 0:00 | 12/31/2016 0:00 |
| 50952154702 | 880944 | 9 | 8 | 1/1/2017 0:00 | 9/30/2017 0:00 |
| 50952154702 | 880944 | 10 | 8 | 10/1/2017 0:00 | 12/31/2017 0:00 |
| 50952154702 | 880944 | 11 | 8 | 1/1/2018 0:00 | 12/31/2018 0:00 |
| 50952154702 | 880944 | 12 | 8 | 1/1/2019 0:00 | 9/30/2021 0:00 |
| 50952154702 | 880944 | 13 | 8 | 10/1/2021 0:00 | 6/30/2022 0:00 |
| 50952154702 | 880944 | 14 | 8 | 7/1/2022 0:00 | 9/30/2022 0:00 |
| 50952154702 | 880944 | 15 | 8 | 10/1/2022 0:00 | 12/31/2022 0:00 |
How do i go through the rows comparing whether the are the same ID and the difference between the end date of one and the start date of the next?
Thanks!
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ldFdDoQgDATgq2x8NqEdKD97FeP9r7GgyyZUNtUHozTxy5TZtkWoCHtE8VjWemL2sX5wfY43OXYgDi96E9VBcZ7aQM7Bvv4loAjpBMP5YxJNw3fjJJJOkUwhqBTpksI2ZEyRL0Q2iTgSpRNwkDoAjQJYQqIm5EwltCVSOzxoZEJkRZiNTIzSjXuNTAQmFcOsZIbwmMPsZGZgNIraBWwTftyl/1LL/hqwjV5NOgnoGDcIUTGgr+OH7B8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Client ID" = _t, #"Contract Number " = _t, Index = _t, #"Index Contract" = _t, #"Contract Start Date" = _t, #"Contract End Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Client ID", Int64.Type}, {"Contract Number ", Int64.Type}, {"Index", Int64.Type}, {"Index Contract", Int64.Type}, {"Contract Start Date", type datetime}, {"Contract End Date", type datetime}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Difference", each if [Index]=1 then null
else if [#"Contract Number "]<>#"Changed Type"{[Index]-2}[#"Contract Number "] then null
else [Contract Start Date]-#"Changed Type"{[Index]-2}[Contract End Date])
in
#"Added Custom"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".