Create End dates based on next start date for a dimension

[Iwin]

Hi,

I have a requirement where I need to create end dates based on start dates.

I have explained the scenario below : 

Here for Patient A ,the end date of Free Use is null but as Commercial Order Type started on  07 /07/2024 ,the End Date for Free Use should display date 1 day before the start date of Commercial Order Type i.e - 06/07/2024

For Patient B, there are 3 Order Types ,and there can be any number of Order Types for a Patient.

How to create the end dates in this scenario?

[VBD · Accepted solution]

Hello, 

You can try something like : Test :
Load Patient,
[Order Type],
[Start Date],
if(Previous(Patient) = Patient and len([End date]) =0 , date(peek([Start Date],-1)-1),[End date]) as [End date]
Resident TMP
Order by Patient asc, [Start Date] desc;

[Iwin]

Thank you for the quick reply and fix.