Hi all
I am new to DAX and wonder if this is something doable using DAX. I have a schedule containing leasing contract and the monthly rental. There are 2 types of contracts - contract type F is fixed which the start and end date are confirmed with tenant while contract type D hasn't been confirmed and can change subject to market condition. Below is a sample schedule:
| row | date | unit | contract_type | contract_start_date | contract_end_date | rent | | |
| 1 | 1/1/2023 | A | F | 1/1/2022 | 4/30/2023 | 100 | | |
| 2 | 2/1/2023 | A | F | 1/1/2022 | 4/30/2023 | 100 | | |
| 3 | 3/1/2023 | A | F | 1/1/2022 | 4/30/2023 | 100 | | |
| 4 | 4/1/2023 | A | F | 1/1/2022 | 4/30/2023 | 100 | | |
| 5 | 5/1/2023 | A | D | 5/20/2023 | 8/19/2023 | 200 | | |
| 6 | 6/1/2023 | A | D | 5/20/2023 | 8/19/2023 | 200 | | |
| 7 | 7/1/2023 | A | D | 5/20/2023 | 8/19/2023 | 200 | | |
| 8 | 8/1/2023 | A | D | 5/20/2023 | 8/19/2023 | 200 | | |
| 9 | 9/1/2023 | A | D | 9/8/2023 | 12/7/2023 | 250 | | |
| 10 | 10/1/2023 | A | D | 9/8/2023 | 12/7/2023 | 250 | | |
Row 1 - 4 has a fixed contract and therefore start and end date has been confirmed and can't be changed.
Row 5 - contract is not fixed yet. Its start date is 20 days after the last contract ends. Duration of contract is 3 months and hence end date is mid Aug.
Row 9 - last contract ends on Aug 19. Its start date is 20 days after the last contract and therefore Sep 8, and end date is 3 months later - Dec 7.
I want to have the flexibility to use parameter to simulate the different number of days in between contract. Instead of 20 days I now change to 25 days with contract duration still remains at 3 months. Would it be possible to use DAX to create the new start and end date? Such parameter should only apply to contract_type as "D"
| row | date | unit | contract_type | contract_start_date | contract_end_date | rent | new_start_date | new_end_date |
| 1 | 1/1/2023 | A | F | 1/1/2022 | 4/30/2023 | 100 | 1/1/2022 | 4/30/2023 |
| 2 | 2/1/2023 | A | F | 1/1/2022 | 4/30/2023 | 100 | 1/1/2022 | 4/30/2023 |
| 3 | 3/1/2023 | A | F | 1/1/2022 | 4/30/2023 | 100 | 1/1/2022 | 4/30/2023 |
| 4 | 4/1/2023 | A | F | 1/1/2022 | 4/30/2023 | 100 | 1/1/2022 | 4/30/2023 |
| 5 | 5/1/2023 | A | D | 5/20/2023 | 8/19/2023 | 200 | 5/25/2023 | 8/24/2023 |
| 6 | 6/1/2023 | A | D | 5/20/2023 | 8/19/2023 | 200 | 5/25/2023 | 8/24/2023 |
| 7 | 7/1/2023 | A | D | 5/20/2023 | 8/19/2023 | 200 | 5/25/2023 | 8/24/2023 |
| 8 | 8/1/2023 | A | D | 5/20/2023 | 8/19/2023 | 200 | 5/25/2023 | 8/24/2023 |
| 9 | 9/1/2023 | A | D | 9/8/2023 | 12/7/2023 | 250 | 9/18/2023 | 12/17/2023 |
| 10 | 10/1/2023 | A | D | 9/8/2023 | 12/7/2023 | 250 | 9/18/2023 | 12/17/2023 |
Row 5 - start date is now May 25, with a 3 months duration, end date is pushed to Aug 24. Subsequently on row 9, the start and end date is moved due to the last contract end date.
It'd be great if the experts here can shed some lights on how this can be done in DAX.
Thanks.
yikl
