Obtain value between 2 dates

[jrscaletta]

Hi all!

I have 2 tables:

1) SALES TABLE

Customer ID	Product ID	Date	Amount €
1	55	03/01/2020	500 €
2	66	01/02/2020	600 €
3	55	01/02/2020	700 €

2) COST TABLE

Product ID	Since Date	To Date	Cost €
55	01/01/2020	31/01/2020	25 €
55	01/02/2020	29/02/2020	30 €
66	01/01/2020	31/07/2020	99 €

Then, I want to obtain COST € for every row of first table.

I am not using date tables.My date filter is "DATE" of table "SALES TABLE". Thanks in advance.

Thanks in advance.

[amitchandak · Accepted solution]

@jrscaletta , new column in sales table

new column: maxx(filter(cost, cost[Product ID] ?sales[Product ID] && Sales[Date]>'cost[From Date] && Sales[Date]<'cost[Date to]),Cost[Cost'])

[v-kelly-msft · Accepted solution]

Hi  @jrscaletta,

Create a column as below:

Column = 
var _cost=CALCULATE(MIN('COST TABLE'[ Cost €]),FILTER(ALL('COST TABLE'),'COST TABLE'[Product ID]='SALES TABLE'[Product ID]&&'COST TABLE'[Since Date]<=EARLIER('SALES TABLE'[Date])&&'COST TABLE'[ To Date]>=EARLIER('SALES TABLE'[Date])))
Return
_cost

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak · Accepted solution]

@jrscaletta , new column in sales table

new column: maxx(filter(cost, cost[Product ID] ?sales[Product ID] && Sales[Date]>'cost[From Date] && Sales[Date]<'cost[Date to]),Cost[Cost'])