Insert duplicate row based on comparing dates across rows

[omillzy]

I have a list of contracts for rental properties.

On each contract is a list of subscription lines detailing how much the customer will be paying across a certain timeframe - indicated by SBQQ__StartDate__c and SBQQ__EndDate__c. In the example below, the customer's contract starts on 26.08.2014 (paying full rent price) but is then offered 'Half Rent' from 26.08.2020 - 25.12.2020

After this period of half rent, the customer is returned back to full rent (SBQQ__ProductName__c = Office) - the problem is that we don't have a unique line/row for this entry. It's salesforce data so there must be some automation on the system which isn't replicated in the underlying dataset.

What I need to do is create a timeline of rental steps which I will then be index by contract & flatten into one line so I would have 1 contract number and all the flattened rental/contract steps (with the amount being payed by the customer at each step) pushed out into a very long wide dataset.

In my head this would be very easy to achieve, especially if I could utilise cell referencing, the logic is something like:
if datediff(SBQQ__EndDate__c[row 1], SBQQ_StartDate__c[row 2]) > 1 then insert a duplicate row of the full rent line (SBQQ_ProductName__c = Office)

In simple english it's basically: "if there is a gap between the end date of the n rental step and start of n+1 rental step then return to full rent during this gap"

Here's the data for one contract and its rental steps. I've also attached a drawn picture to show how the concept is working in theory. I've got the code working to index by contract and flatten using SUMMARIZE() in DAX, I just need to insert a duplicate row each time there is a gap in the 'half rent' steps (or there may be a more elegant solution)

Subscription LineName	SBQQ__ContractNumber__c	SBQQ__ProductName__c	SBQQ__StartDate__c	SBQQ__EndDate__c	Total_Annualised_Rent__c	Total_Annual_Billable_Amount__c
SUB-0006442	00002588	Office	26/08/2014	25/08/2024	56875	0
SUB-0017045	00002588	Half Rent	26/08/2020	25/12/2020	null	28437.49
SUB-0017046	00002588	Half Rent	26/08/2021	25/12/2021	null	28437.49
SUB-0017047	00002588	Half Rent	26/08/2022	25/12/2022	null	28437.49
SUB-0017048	00002588	Half Rent	26/08/2023	25/01/2024	null	28437.496

[v-kelly-msft]

Hi @omillzy ,

Do you wanna get the column which is marked in red as below?(If not,advise me what is your expected output)

If so ,first go to "edit queries">"Add column">"Index column">"From 1"

Then you need a calculated column as below:

Column = 
var a=CALCULATE(MAX('Table'[SBQQ__EndDate__c]),'Table'[Index]=EARLIER('Table'[Index]),ALLEXCEPT('Table','Table'[SBQQ__ContractNumber__c]))
VAR b=CALCULATE(MAX('Table'[SBQQ__StartDate__c]),'Table'[Index]=EARLIER('Table'[Index])+1,ALLEXCEPT('Table','Table'[SBQQ__ContractNumber__c]))
Return
SWITCH(TRUE(),'Table'[Index]=1,"Office",DATEDIFF(b,a,DAY)<>1,"half rent",BLANK())

Then you will see :

For the related .pbix file,pls click here.

Best Regards,
Kelly

[omillzy]

@v-kelly-msft thanks for the quick response - unfortunately I already have the product name column in my dataset.

My expected output would be something like this

contract number	productname	start date	end date
00002588	office (full rent)	8/26/2014	8/25/2020
00002588	half rent	8/26/2020	12/25/2020
00002588	office (full rent)	12/26/2020	8/25/2021
00002588	half rent	8/26/2021	12/25/2021
00002588	office (full rent)	…	…
...	…	…	…

So I need some way to compare the end date of the half rent and check if the next step is directly after it (less than 1 day), if it's not (if there is a gap > 1 day), then we need to jump back up to full rent until the next step. This behaviour will continue untill all the rent steps have been actioned, and the final step would be the remainder of the office/full rent line.

You can assume the first line, per contract, will be the full rent line. Hope that makes sense, thanks alot for your help