Compare two overlapping sets of date ranges
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.