Forum Discussion
Look up value based on date range
Tring to Look up an offer value with in adate range
Table - Offer Table - values with offers with different date ranges
| Supplier | Product | StartDate | EndDate | Offer |
| SUP1 | PRD1 | 1/1/2023 | 1/10/2023 | $5 |
| SUP1 | PRD1 | 1/11/2023 | 1/15/2023 | $6 |
| SUP2 | PRD2 | 1/1/2023 | 1/31/2023 | $10 |
Fact Table - Purchasing Table - With Purchase data
| PO | Date | Product | Supplier |
| 1001 | 1/4/2023 | PRD1 | SUP1 |
| 1002 | 1/5/2023 | PRD2 | SUP2 |
| 1003 | 1/11/2023 | PRD1 | SUP1 |
Expected Result - Need to pick the offer price based on the combination of Product & SUpplier for the Date that falls in the date range in Offer table.
Tried using Lookupvalue, but date range was an issue. How can i use calculated column to solution it. i will need a Calculated column as i have to use a slicer also to identify Offer exist or not.
| PO | Date | Product | Supplier | Offer |
| 1001 | 1/4/2023 | PRD1 | SUP1 | $5 |
| 1002 | 1/5/2023 | PRD2 | SUP2 | $10 |
| 1003 | 1/11/2023 | PRD1 | SUP1 | $6 |
Thanks
Anonymous , A new column fact table
maxx(filter(offfer, offer[Supplier] = fact[Supplier] && offer[product] = fact[product] && Fact[Date] >= offer[Start Date] && Fact[Date] <= offer[End Date] ), offer[offer])
2 Replies
Anonymous , A new column fact table
maxx(filter(offfer, offer[Supplier] = fact[Supplier] && offer[product] = fact[product] && Fact[Date] >= offer[Start Date] && Fact[Date] <= offer[End Date] ), offer[offer])
Thanks amitchandak