Hi - thought I could nail this, but it keeps going wrong. Might be more complicated than I thought. Looking for your ideas.
Trying to figure this out using Power Query and Power Pivot / Dax in Excel;
Consider this setup; 1) A list of dates that represents rentals of a sort. 2) A second list of dates that represents a rebate period.
The condition for rebate is 120 days per rebate period. At the moment rebate periods are for one year, thus 120 days within a year. Starting anywhere in a year (not following calendar year, but initiated by first rental after 01-10-2024. Following this the rebate maximum could be reached inside of a rental period when reaching 120 days. Part of the rental with rebate, part without. Furthermore there could be more than one rental at a given date, but it still only uses one day of rebate.
Might be easier with some example data as it looks after preparation in Power
Query;
1) List of rentals from 3 customers (id):
| Id | Rents | Rental/fiscal Year | Rent start | Rent end | Disp |
| 1 | sku | 2024 | 29-04-2024 | 06-05-2024 | 18000 |
| 1 | po | 2024 | 06-05-2024 | 26-06-2024 | 90000 |
| 1 | he | 2024 | 26-06-2024 | 04-09-2024 | 110000 |
| 1 | skn | 2024 | 04-09-2024 | 08-09-2024 | 6000 |
| 1 | skf | 2024 | 09-09-2024 | 31-12-9998 | 60000 |
| 2 | sa | 2024 | 16-09-2022 | 31-12-9998 | 400000 |
| 2 | sa | 2023 | 16-09-2022 | 31-12-2023 | 150000 |
| 3 | ka | 2024 | 13-05-2024 | 31-12-9998 | 320000 |
Disp is the cost in the rental/fiscal year.
2) Rebate periods:
| Id | Rebate scheme | Rebate start | Rebate end |
| 1 | Rebate 120 | 29-04-2024 | 31-12-9999 |
| 2 | Rebate 120 | 01-10-2024 | 31-12-9999 |
| 2 | Rebate 120 | 01-10-2023 | 30-09-2024 |
| 3 | Rebate 120 | 13-05-2024 | 31-12-9999 |
Only one rebate scheme at the moment and always runs for one year after start date. Thus rebate end is not needed, but maybe other rebate schemes will surface later.
I believe I need to join these two tables either in Power Query or in the data model in Power Pivot.
If joined i would like to be able to get something like this for customer 2;
| Id | Rebate scheme | Rebate start | Rebate end | Rents | Rental/fiscal Year | Rent start | Rent end | Disp | Year 2023 | Year 2024 | |
| 2 | Rebate 120 | 01-10-2023 | 30-09-2024 | sa | 2023 | 16-09-2022 | 31-12-2023 | 150000 | 92 | 0 | (92 days of 120 spend in 2023) |
| 2 | Rebate 120 | 01-10-2023 | 30-09-2024 | sa | 2024 | 16-09-2022 | 31-12-9998 | 400000 | 0 | 28 | (28 - up to cap - spend in 2024) |
| 2 | Rebate 120 | 01-10-2024 | 31-12-9999 | sa | 2023 | 16-09-2022 | 31-12-2023 | 150000 | 0 | 0 | (obsolete line - no overlap) |
| 2 | Rebate 120 | 01-10-2024 | 31-12-9999 | sa | 2024 | 16-09-2022 | 31-12-9998 | 400000 | 0 | 28 | (same 28 as in line 2!!!) |
For customer 1 something like this:
| id | Rebate scheme | Rebate start | Rebate end | Rents | Rental/fiscal Year | Rent start | Rent end | Disp | 2024 | 2025 |
| 1 | Rebate 120 | 29-04-2024 | 31-12-9999 | sku | 2024 | 29-04-2024 | 06-05-2024 | 18000 | 8 | 0 |
| 1 | Rebate 120 | 29-04-2024 | 31-12-9999 | po | 2024 | 06-05-2024 | 26-06-2024 | 90000 | 52 | 0 |
| 1 | Rebate 120 | 29-04-2024 | 31-12-9999 | he | 2024 | 26-06-2024 | 04-09-2024 | 110000 | 62 | 0 |
| 1 | Rebate 120 | 29-04-2024 | 31-12-9999 | skn | 2024 | 04-09-2024 | 08-09-2024 | 6000 | 0 | 0 |
| 1 | Rebate 120 | 29-04-2024 | 31-12-9999 | skf | 2024 | 09-09-2024 | 31-12-9998 | 60000 | 0 | 0 |
Customer 1 has spend he/hers 120 days within rental in line 3. No new rebate scheme entered for the period after 28-04-2025.
Thank you for reading to the end and I am looking forward to any hints you might be able to give me. Tried a bit with chatgpt, but could not nail it - or my prompting are not clear enough.
