Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
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
@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
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |