Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

How to get a price based on a date range

Hello, I have two tables, as following:   1) Table 1:  Item Retail Price Valid From Date Valid To Date   An item can have more than one Retail Price, depending on the validity within the per...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I actually got a solution mixing both your solutions, and it seems to be working 🙂

     

    Retail Price =
    VAR CurrentItem = Table2[Item]
    VAR CurrentDate = Table2[Invoice Date]
    RETURN
    MAXX(FILTER(RELATEDTABLE(Table1), CurrentItem = Table1[Item] && Table1[From Date] <= CurrentDate && Table1[To Date] >= CurrentDate), Table1[Retail Price])

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
6 years ago

Anonymous ,

 

You can create a calculate column in table2 as below:

Retail Price = 
VAR Current_Date = 'Table'[Invoice Date]
VAR Current_Item = 'Table'[Item]
RETURN
CALCULATE(MAX('Table1'[Retail Price]), FILTER(Table1, Table1[Item] = Current_Item && Table1[Valid From Date] <= Current_Date && Table1[Valid To Date] >= Current_Date))

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello v-yuta-msft , thanks for your reply!

    I tried your solution but got the following error:

     

    "A single value for column 'Retail Price' in table 'Table1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

     

    On table 2 (invoice history by date) I have many lines with the same item, because we have sales happening several times, so I'm not sure if this could be what's causing the issue?