Look up value based on date range

[Anonymous]

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

[amitchandak · Accepted solution]

@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])

[Anonymous]

Thanks @amitchandak