Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
Hi @omillzy
You could try Power KPI.
See a similar thread:
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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |