Creating a flattened timeline based on dates in multiple 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-lid-msft · Accepted solution]

Hi @omillzy ,

Sorry for our late reply, We can create a measure used in line chart to meet your requirement:

Value = 
VAR T =
    FILTER (
        'Table',
        [SBQQ__StartDate__c] <= SELECTEDVALUE ( 'Calendar Table'[Date] )
            && [SBQQ__EndDate__c] >= SELECTEDVALUE ( 'Calendar Table'[Date] )
            && 'Table'[SBQQ__ProductName__c] = "Half Rent"
    )
RETURN
    IF (
        CALCULATE ( COUNTROWS ( 'Table' ), T ) > 0,
        CALCULATE ( SUM ( 'Table'[Total_Annual_Billable_Amount__c] ), T ),
        CALCULATE (
            SUM ( 'Table'[Total_Annualised_Rent__c] ),
            'Table'[SBQQ__ProductName__c] = "Office"
        )
    )

Best regards,