Create rental steps by 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

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-juanli-msft]

Hi @omillzy 

You could try Power KPI.

See a similar thread:

https://community.powerbi.com/t5/Desktop/Stepped-line-chart-changing-the-course-in-the-middle-of-two...

Besides, i am trying to reproduce your problem, is the table below correct as your actual one?

number	productname	start date	end date
1	full rent	8/26/2014	8/25/2024
1	half rent	8/26/2020	12/25/2020
1	half rent	8/26/2021	12/25/2021
1	half rent	8/26/2022	12/25/2022
1	half rent	8/26/2023	1/25/2024
2	full rent	8/26/2019	8/25/2022
2	half rent	8/26/2020	12/25/2020
2	half rent	8/26/2021	1/25/2022

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[omillzy]

@v-juanli-msft Yes that custom visual looks good, however, I just want to get the data into an appropriate format for excel export. Further down the line I may use the stepped line visual.

And yes, the table is correct. Using your table, I would want it to look like the following format (i haven't filled out every step)

contract number	productname	start date	end date	amount (£)
1	full rent	8/26/2014	8/25/2020	60k
1	half rent	8/26/2020	12/25/2020	30k
1	full rent	12/26/2020	8/25/2021	60k
1	half rent	8/26/2021	12/25/2021	30k
1	full rent	…	…	…
	…	…	…	…
2	full rent	8/26/2019	8/25/2020	70k
2	half rent	8/26/2020	12/25/2020	35k
	…	…	…	…

The key assumptions are:

a) Full rent will always be the first step on a contract

b) amount (£) is a field direct from datasource, no need to calc

[v-juanli-msft · Accepted solution]

Hi @omillzy 

In your table,

1. open Edit queries, first sort by [number], second sort by [start date], then add an index column from 1.

2. close&&apply

3. create calculated columns

flag =
VAR min_ =
    CALCULATE (
        MIN ( 'Table'[Index] ),
        ALLEXCEPT (
            'Table',
            'Table'[number]
        )
    )
VAR max_ =
    CALCULATE (
        MAX ( 'Table'[Index] ),
        ALLEXCEPT (
            'Table',
            'Table'[number]
        )
    )
RETURN
    SWITCH (
        [Index],
        min_, "min",
        max_, "max"
    )

add start =
SWITCH (
    TRUE (),
    [flag] <> "min"
        && [productname] = "half rent", [end date] + 1,
    [flag] = "min", [start date]
)


add end =
CALCULATE (
    MIN ( 'Table'[start date] ),
    FILTER (
        ALLEXCEPT (
            'Table',
            'Table'[number]
        ),
        'Table'[Index]
            = EARLIER ( 'Table'[Index] ) + 1
    )
) - 1

4.create a new table with dax codes below

Table 3 =
UNION (
    FILTER (
        SUMMARIZE (
            'Table',
            [number],
            'Table'[productname],
            'Table'[start date],
            [end date],
            'Table'[amount]
        ),
        [productname] <> "full rent"
    ),
    SELECTCOLUMNS (
        'Table',
        "number", [number],
        "productname", "full rent",
        "start date", [add start],
        "end date", [add end],
        "amount", [amount]
    )
)

5.add columns to a table visual, then "export data".

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.