Forum Discussion
Lookup start date and end date from another table
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
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.
4 Replies
- lbendlin
Super User
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)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
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.