Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Create rental steps by comparing dates across rows

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 LineNameSBQQ__ContractNumber__cSBQQ__ProductName__cSBQQ__StartDate__cSBQQ__EndDate__cTotal_Annualised_Rent__cTotal_Annual_Billable_Amount__c
SUB-000644200002588Office26/08/201425/08/2024568750
SUB-001704500002588Half Rent26/08/202025/12/2020null28437.49
SUB-001704600002588Half Rent26/08/202125/12/2021null28437.49
SUB-001704700002588Half Rent26/08/202225/12/2022null28437.49
SUB-001704800002588Half Rent26/08/202325/01/2024null28437.496
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi Anonymous 

    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.

     

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous 

    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/td-p/531754

     

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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 numberproductnamestart dateend dateamount (£)
      1full rent8/26/20148/25/202060k
      1half rent8/26/202012/25/202030k
      1full rent12/26/20208/25/202160k
      1half rent8/26/202112/25/202130k
      1full rent………
       …………
      2full rent8/26/20198/25/202070k
      2half rent8/26/202012/25/202035k
       …………

       

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous 

        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.