How to get a price based on a date range

[Anonymous]

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 period range.

2) Table 2:

Item

Invoice Date

I would like to get the Retail Price from Table 1 and add it to Table 2 by finding the Invoice Date within the period range from Table 1.

I'm quite new to PBI, so I'm struggling to find a solution.

Can you experts please shed some light onto this? 🙂

Thank you!!

[Anonymous · Accepted solution]

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

[Greg_Deckler]

I guess I made the assumption that the tables were related on item.

[v-yuta-msft]

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

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? 

[Greg_Deckler]

Perhaps something like:

Price Column = 
    MAXX(
      FILTER(
        RELATEDTABLE('Table 1'),
        'Table 1'[Valid From Date] <= 'Table 2'[Invoice Date] && 'Table 1'[Valid To Date] >= 'Table 2'[Invoice Date]
      ),
      [Retail Price]
    )

[Daviejoe]

This was exactly what I was looking for! Thanks @Greg_Deckler 

[Anonymous]

Hello @Greg_Deckler , thanks for your reply!

Your solution gave me only the highest value based on the date, but not on the item. I think it might be missing a filter somewhere in between...