cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giuliapiazza94
Helper III
Helper III

Price list problem

I have a price list like this 

giuliapiazza94_3-1631614254098.png

 

and I have a sell table 

giuliapiazza94_5-1631614307393.png

The final table should be like this

 

giuliapiazza94_6-1631614324851.png

 

I have created a key-word "product_date" --> for example a_05/2020 or a_07/2020 but I don't know how connect key-words without connection.

Have you some ideas?

 

Thank you all

 

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@giuliapiazza94 This is essentially Lookup Min/Max: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

 

So, in your case:

Column = 
  VAR __Date = 'SELL'[date]
  VAR __Product = 'SELL'[product]
  VAR __PriceDate = MAXX(FILTER('Table-Price',[product]=__Product && [date]<=__Date),[date])
RETURN
  MAXX(FILTER('Table-Price',[product]=__Product && [date]=__PriceDate),[price])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

@giuliapiazza94 This is essentially Lookup Min/Max: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434

 

So, in your case:

Column = 
  VAR __Date = 'SELL'[date]
  VAR __Product = 'SELL'[product]
  VAR __PriceDate = MAXX(FILTER('Table-Price',[product]=__Product && [date]<=__Date),[date])
RETURN
  MAXX(FILTER('Table-Price',[product]=__Product && [date]=__PriceDate),[price])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

@Greg_Deckler you are a genius 😊

thank you very much

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Top Solution Authors