cancel
Showing results for
Did you mean:
Frequent Visitor

## 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 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!!

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

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

7 REPLIES 7
Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

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

Proud to be a Super User!

Community Support

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.

Frequent Visitor

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?

Frequent Visitor

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

Frequent Visitor

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

Super User IV

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

---------------------------------------

@ me in replies or I'll lose your thread!!!

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

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

Proud to be a Super User!

Super User I

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

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

Proud to be a Super User!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors