Filter table based on dates from another table

[bingbadabom]

I have one table that i use Slicers on to reduce it to one now.

It looks something like this:

id	start_date	end_date
1	2022-02-02T00:00:00	2022-02-05T00:00:00

I have a second table containg rows with dates that occur between the start- and end-date.

id	product	sales	date
1	a	5	2022-02-02T02:00:00
2	b	10	2022-02-03T02:00:00
3	c	20	2022-02-06T02:00:00

What i want is to present visuals with the second table as a source..

How can i do that? I've tried searching similar solutions, but they always do some kind of summary of the data, and that's not what i need.  I need to make (for example) a column chart showing summary of sales per product within the date interval.

Edit: There's no relations between the two tables other than the dates

[bingbadabom]

Most amazing, thank you so much!

[v-yueyunzh-msft · Accepted solution]

Hi , @bingbadabom 

Thanks for your sample data first!

According to your description, you want to filter the table by the slicer .

Here are the steps you can refer to :
(1)This is my test data:

(2)We can create a measure like this:

Measure = var _start_date = MAX('Table1'[start_date])
var _end_date = MAX('Table1'[end_date])
var _t = FILTER('Table2','Table2'[date]>=_start_date && 'Table2'[date]<=_end_date)
return
SUMX(_t,[sales])

(3)Then we can put the fields we need on the visual and the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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

[bingbadabom]

Just a follow up. Supposed the id for the first table is not unique. Due to some joins in power query they are duplicated. Your suggested solution still works with this change.
But there's a new colum referencing a third table by value_id (i could not find a product/sales analogy for this 😅).

So if the row with id 1 from the first table is selected, i need to filter out values in the third table that has 'value_id' 1 and 2. I could only get your example to work with one of the values.