Forum Discussion

jrscaletta's avatar
jrscaletta
Helper II
6 years ago
Solved

Obtain value between 2 dates

Hi all!

 

I have 2 tables:

 

1) SALES TABLE

Customer IDProduct IDDate  Amount €
1 55 03/01/2020 500 €
2 66 01/02/2020 600 €
3 55 01/02/2020 700 €

 

2) COST TABLE

Product IDSince Date To Date Cost €
5501/01/2020 31/01/2020  25 €
5501/02/2020 29/02/2020  30 €
6601/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.

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    @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's avatar
    v-kelly-msft
    6 years ago

    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!

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    @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's avatar
    v-kelly-msft
    Community Support
    6 years ago

    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!