Conditional based on difference between current and previous row from different columns

[jcatswi]

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 table, the same client can have several contracts, so I need to compute for all clients, what is the difference between the end of one contract and the beginning of the next one (in bold). That is, difference between current and previous row from different columns. 

Then, I would like to create a column called Time Lapsed (the desired columns shown in italics). This is a conditional which uses that computed difference and has several cases. This is not defined for the first contract. For (current) clients who only have one contract this is 0.  How do I create this conditional (switch?) with indexes from two different rows and columns?

I thought about something like that but I do not know how to implement it in Power BI :

If

Client ID (index) = Client ID (index -1)  

Case 1: If Contract End empty then  Time Lapsed = 0

Case 2: Time Lapsed = Contract Start (index) – Contract End (index-1)

Else

Time Lapsed = NA

At the end, I need to classify clients by status, i. e. created another column, where I would have the following cases:

1: Left: If Client ID (index) = Client ID (index -1) and (Time Lapse(index) = 1 or Time Lapse(index) = NA) and Contract end (index) < Today

2: Current: If Client ID (index) = Client ID (index -1) and Time Lapse(index) = 1 and

Contract end (index) Empty

3: Returned Left: If Client ID (index) = Client ID (index -1) and Time Lapse(index) > 1 and Contract end (index) < Today

4: Returned Current: If Client ID (index) = Client ID (index -1) and Time Lapse(index) < 1 and Contract end (index) empty

Both columns required similar construction, which requires a conditional based on comparing current and previous row from different columns, and I do not how to do this in Power BI. I would appreciate any help on this. I have seen several solutions on difference betwenn current and previos row but not with a conditional based on that. 

Client ID	Contract Number	Contract Start Date	Contract End Date	Time Lapsed	Status
50951328184	543972	01/10/14	31/12/16	NA
50951328184	515577	01/01/17	30/09/17	1	Left
50951502704	515580	01/10/17	31/12/17	NA
50951502704	514651	01/01/18		1	Current
50951362629	745280	01/01/19	31/12/19	NA
50951362629	543972	01/01/20	31/12/20	1
50951362629	515211	01/01/22	30/09/22	365	Returned Left
50951948031	744544	01/10/21	30/06/22	NA
50951948031	121237	01/07/22	31/12/22	1
50951948031	455899	01/02/23	31/08/22	30
50951948031	564654	01/10/2023		0	Returned Current
50951265039	158898	01/10/17		0	Current
50951271747	685645	01/07/22	01/07/23	NA	Left

[lbendlin]

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. 

[jcatswi]

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!

[lbendlin]

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