New column that checks if date is in multiple dynamic date ranges

[bernate]

Hello, I have a list of dates I would like to use as slicer selections.  

I am using 2 tables- Date and Product Archive. The Product Archive table has 2 date columns- a Start Date and an Archive Date. The Date table has a DtKey column. I want to add a column to the Date table that would have the associated Start Date. So for example, in the Date table, all dates that fall between 02/21/2019 and 09/08/2021 should have "02/21/2019" listed in the new column, all dates between 09/09/2019 and 11/03/2019 should have "09/09/2019" listed in the new column.

I can do this manually with the code below, but I want this to be dynamic so that when a new Start Date gets added to the Product Archive, Power BI will also update accordingly.

if [DtKey] >= #date(2000,01,01) and [DtKey] < #date(2019,02,21) then "01/01/2000" else 

if [DtKey] >= #date(2019,02,21) and [DtKey] < #date(2019,09,09) then "02/21/2019" else "Next"

Power BI file available here https://drive.google.com/drive/folders/1ueuzxnHDi8fnba03ijc74P0oHSIo6KYC?usp=sharing

[bernate]

Thank you for your help Stephen!

[v-stephen-msft · Accepted solution]

Hi @bernate ,

Really happy to help you solve this. Here's my solution below.

1.Go through a few steps to get the next date.(reference:Value from previous row )

2.In Date Table, reference the start date field and the next start date field from Product Archive table.

3.Expand it.

4.Create below custom for filtering. Filter out 1.

And here's your final column you want.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.