Forum Discussion
Insert duplicate row based on comparing dates across rows
Community SupportHi Anonymous ,
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