cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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])

View solution in original post

7 REPLIES 7
Super User IV
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
Community Support

@emaioudamari ,

 

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

Capture.PNG 

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.

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? 

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

View solution in original post

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!




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!




Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

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