topic Re: Compare two overlapping sets of date ranges in DAX Commands and Tips https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Compare-two-overlapping-sets-of-date-ranges/m-p/4242022#M167935 <P>Hi Ibendlin</P><P>&nbsp;</P><P><EM>Your id 2 has two rows with Rents sa . Is that intended? Why is Rent start for both in 2022?</EM></P><P>- it is really one rent, but the data comes from two different economic years. In 2023 you expect 150.000 and in 2024 400.000. The rent begins 16-09-2022, but it not until 01-10-2023 I need to count to a cap of 120 days/rebate period.&nbsp;</P><P>&nbsp;</P> Mon, 14 Oct 2024 21:44:18 GMT Anonymous 2024-10-14T21:44:18Z Compare two overlapping sets of date ranges https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Compare-two-overlapping-sets-of-date-ranges/m-p/4241892#M167930 <P class="lia-indent-padding-left-30px">Hi - thought I could nail this, but it keeps going wrong. Might be more complicated than I thought. Looking for your ideas.&nbsp;</P><P class="lia-indent-padding-left-30px">Trying to figure this out using Power Query and Power Pivot / Dax in Excel;</P><P class="lia-indent-padding-left-30px">&nbsp;</P><P class="lia-indent-padding-left-30px">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.&nbsp;</P><P class="lia-indent-padding-left-30px">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.</P><P class="lia-indent-padding-left-30px">&nbsp;</P><P class="lia-indent-padding-left-30px">Might be easier with some example data as it looks after preparation in Power</P><P class="lia-indent-padding-left-30px">Query;</P><P class="lia-indent-padding-left-30px">&nbsp;</P><P class="lia-indent-padding-left-30px">1) List of rentals from 3 customers (id):</P><TABLE><TBODY><TR><TD>Id</TD><TD>Rents</TD><TD>Rental/fiscal Year</TD><TD>Rent start</TD><TD>Rent end</TD><TD>Disp</TD></TR><TR><TD>1</TD><TD>sku</TD><TD>2024</TD><TD>29-04-2024</TD><TD>06-05-2024</TD><TD>18000</TD></TR><TR><TD>1</TD><TD>po</TD><TD>2024</TD><TD>06-05-2024</TD><TD>26-06-2024</TD><TD>90000</TD></TR><TR><TD>1</TD><TD>he</TD><TD>2024</TD><TD>26-06-2024</TD><TD>04-09-2024</TD><TD>110000</TD></TR><TR><TD>1</TD><TD>skn</TD><TD>2024</TD><TD>04-09-2024</TD><TD>08-09-2024</TD><TD>6000</TD></TR><TR><TD>1</TD><TD>skf</TD><TD>2024</TD><TD>09-09-2024</TD><TD>31-12-9998</TD><TD>60000</TD></TR><TR><TD>2</TD><TD>sa</TD><TD>2024</TD><TD>16-09-2022</TD><TD>31-12-9998</TD><TD>400000</TD></TR><TR><TD>2</TD><TD>sa</TD><TD>2023</TD><TD>16-09-2022</TD><TD>31-12-2023</TD><TD>150000</TD></TR><TR><TD>3</TD><TD>ka</TD><TD>2024</TD><TD>13-05-2024</TD><TD>31-12-9998</TD><TD>320000</TD></TR></TBODY></TABLE><P class="lia-indent-padding-left-30px">Disp is the cost in the rental/fiscal year.&nbsp;</P><P class="lia-indent-padding-left-30px">&nbsp;</P><P class="lia-indent-padding-left-30px">2) Rebate periods:</P><TABLE><TBODY><TR><TD>Id</TD><TD>Rebate scheme</TD><TD>Rebate start</TD><TD>Rebate end</TD></TR><TR><TD>1</TD><TD>Rebate 120</TD><TD>29-04-2024</TD><TD>31-12-9999</TD></TR><TR><TD>2</TD><TD>Rebate 120</TD><TD>01-10-2024</TD><TD>31-12-9999</TD></TR><TR><TD>2</TD><TD>Rebate 120</TD><TD>01-10-2023</TD><TD>30-09-2024</TD></TR><TR><TD>3</TD><TD>Rebate 120</TD><TD>13-05-2024</TD><TD>31-12-9999</TD></TR></TBODY></TABLE><P>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.</P><P>&nbsp;</P><P>I believe I need to join these two tables either in Power Query or in the data model in Power Pivot.&nbsp;</P><P>&nbsp;</P><P>If joined i would like to be able to get something like this for customer 2;</P><TABLE><TBODY><TR><TD>Id</TD><TD>Rebate scheme</TD><TD>Rebate start</TD><TD>Rebate end</TD><TD>Rents</TD><TD>Rental/fiscal Year</TD><TD>Rent start</TD><TD>Rent end</TD><TD>Disp</TD><TD>Year 2023</TD><TD>Year 2024</TD><TD>&nbsp;</TD></TR><TR><TD>2</TD><TD>Rebate 120</TD><TD>01-10-2023</TD><TD>30-09-2024</TD><TD>sa</TD><TD>2023</TD><TD>16-09-2022</TD><TD>31-12-2023</TD><TD>150000</TD><TD>92</TD><TD>0</TD><TD>(92 days of 120 spend in 2023)</TD></TR><TR><TD>2</TD><TD>Rebate 120</TD><TD>01-10-2023</TD><TD>30-09-2024</TD><TD>sa</TD><TD>2024</TD><TD>16-09-2022</TD><TD>31-12-9998</TD><TD>400000</TD><TD>0</TD><TD>28</TD><TD>(28 - up to cap - spend in 2024)</TD></TR><TR><TD>2</TD><TD>Rebate 120</TD><TD>01-10-2024</TD><TD>31-12-9999</TD><TD>sa</TD><TD>2023</TD><TD>16-09-2022</TD><TD>31-12-2023</TD><TD>150000</TD><TD>0</TD><TD>0</TD><TD>(obsolete line - no overlap)</TD></TR><TR><TD>2</TD><TD>Rebate 120</TD><TD>01-10-2024</TD><TD>31-12-9999</TD><TD>sa</TD><TD>2024</TD><TD>16-09-2022</TD><TD>31-12-9998</TD><TD>400000</TD><TD>0</TD><TD>28</TD><TD>(same 28 as in line 2!!!)</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>For customer 1 something like this:</P><TABLE><TBODY><TR><TD>id</TD><TD>Rebate scheme</TD><TD>Rebate start</TD><TD>Rebate end</TD><TD>Rents</TD><TD>Rental/fiscal Year</TD><TD>Rent start</TD><TD>Rent end</TD><TD>Disp</TD><TD>2024</TD><TD>2025</TD></TR><TR><TD>1</TD><TD>Rebate 120</TD><TD>29-04-2024</TD><TD>31-12-9999</TD><TD>sku</TD><TD>2024</TD><TD>29-04-2024</TD><TD>06-05-2024</TD><TD>18000</TD><TD>8</TD><TD>0</TD></TR><TR><TD>1</TD><TD>Rebate 120</TD><TD>29-04-2024</TD><TD>31-12-9999</TD><TD>po</TD><TD>2024</TD><TD>06-05-2024</TD><TD>26-06-2024</TD><TD>90000</TD><TD>52</TD><TD>0</TD></TR><TR><TD>1</TD><TD>Rebate 120</TD><TD>29-04-2024</TD><TD>31-12-9999</TD><TD>he</TD><TD>2024</TD><TD>26-06-2024</TD><TD>04-09-2024</TD><TD>110000</TD><TD>62</TD><TD>0</TD></TR><TR><TD>1</TD><TD>Rebate 120</TD><TD>29-04-2024</TD><TD>31-12-9999</TD><TD>skn</TD><TD>2024</TD><TD>04-09-2024</TD><TD>08-09-2024</TD><TD>6000</TD><TD>0</TD><TD>0</TD></TR><TR><TD>1</TD><TD>Rebate 120</TD><TD>29-04-2024</TD><TD>31-12-9999</TD><TD>skf</TD><TD>2024</TD><TD>09-09-2024</TD><TD>31-12-9998</TD><TD>60000</TD><TD>0</TD><TD>0</TD></TR></TBODY></TABLE><P>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.</P><P>&nbsp;</P><P>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.</P> Mon, 14 Oct 2024 18:26:13 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Compare-two-overlapping-sets-of-date-ranges/m-p/4241892#M167930 Anonymous 2024-10-14T18:26:13Z Re: Compare two overlapping sets of date ranges https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Compare-two-overlapping-sets-of-date-ranges/m-p/4242009#M167934 <P>Do not use fake dates.&nbsp; Leave these blank.&nbsp; Otherwise you risk wasting enormous amounts of storage space on date hierarchies.</P> <P>&nbsp;</P> <P>Your id 2 has two rows with Rents sa . Is that intended? Why is Rent start for both in 2022?</P> Mon, 14 Oct 2024 21:20:08 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Compare-two-overlapping-sets-of-date-ranges/m-p/4242009#M167934 lbendlin 2024-10-14T21:20:08Z Re: Compare two overlapping sets of date ranges https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Compare-two-overlapping-sets-of-date-ranges/m-p/4242022#M167935 <P>Hi Ibendlin</P><P>&nbsp;</P><P><EM>Your id 2 has two rows with Rents sa . Is that intended? Why is Rent start for both in 2022?</EM></P><P>- it is really one rent, but the data comes from two different economic years. In 2023 you expect 150.000 and in 2024 400.000. The rent begins 16-09-2022, but it not until 01-10-2023 I need to count to a cap of 120 days/rebate period.&nbsp;</P><P>&nbsp;</P> Mon, 14 Oct 2024 21:44:18 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Compare-two-overlapping-sets-of-date-ranges/m-p/4242022#M167935 Anonymous 2024-10-14T21:44:18Z