Lookup start date and end date from another table

[celenius]

Hi, 

I got two tables, a calendar table (A) and table showing products' start and end dates (B). How can I get the result as shown in table C below (first date in the list would be current date and last date from the Product table, all dates in between should be listed)  

Calendar (A) 

Date

1/1/2020

1/2/2020

1/3/2020...

Product table (B)

Product	Start Date	End Date
A	6/1/2020	9/30/2020
B	9/1/2020	9/30/2020
C	1/1/2021	12/31/2021

Result (C)

Date	Product 1	Product 2	Product 3
6/7/2020	A	null	null
6/8/2020	A	null	null
...
9/1/2020	A	B	null
...
1/1/2021	null	null	C
...
12/31/2021	null	null	C

Best regards,

Chris

[lbendlin]

Pseudo code here

var d= max(date)

var Res = calculate(max([product]),[product start date]<=d,[product end date]>=d)

return res

This will result in the product name if in range, and BLANK if not.

EDIT: actual code here:  Note: no relationships between the tables!

live = 
var d = max('Calendar'[Date])
return CALCULATE(max('Product'[Product]),'Product'[Start Date]<=d,'Product'[End Date]>=d)

[celenius]

Hi Ibendlin,

and thanks for your input. I tried your measure, but it does only return one product for a specific date even though there might be several products with the same starting date? What am I doing wrong? 

However, I was thinking of how to solve this in M as a new table and not DAX as I need to make some further calculations to the data once I get this first step sorted out.

Best Regards,

Christer 

[lbendlin · Accepted solution]

the measure works fine for me. Have you put your fields in the right place? Date is from Calendar, Product is from Product. No linkage.

[celenius]

Now I got it to work after seeing your formula. This solution will work, thanks a lot!

Chris