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...
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?Power BI does not guarantee row numbering. You must bring your own index column to definitively indicate how you want your rows sorted.
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".