Forum Discussion
Get most recent value for each category from another table
Hi everybody,
I have the following problem and can't figure out how to do it. Tried multiple things and can't find a solution...
So I have a SALES table, an ARTICLE table, a RETAIL_PRICE table (and a CALENDAR table):
SALES
ARTICLES
RETAIL_PRICE
Tables are linked to a model, of course.
Now what I'm trying to get is the last valid price for every supplier and article for a given Sales event. So for example:
On Jan. 4th, I sold rocks from supplier B for $18.
Now I need to get the retail price for that (e.g. in order to calculate the discount).
Since the the last retail price update was on Jan. 1st, the most recent retail price is $20.
Now when I try to display this in a table, PowerBI can't find the value (of course), since on Jan. 4th, there is no value for
Supplier B/Rocks. So I need to find the last value (from Jan. 1st) and display it along with the corresponding line in SALES.
To sum up, the desired output would look like:
My original tables are quite huge, so this is a minimal example. Any ideas on how to takle that problem. I tried messing around with LASTDATE(), LASTNONBLANKVALUE() etc., but can't get it quite right.
Thank for you help in advance!!
benjaminpichl , I think you need a new column in sales table
New column =
var _max = maxx(filter(RETAIL_PRICE, RETAIL_PRICE[Supplier] =sales[Supplier] && RETAIL_PRICE[Article] = Sales[Article] && RETAIL_PRICE[Valid_from] <= Sales[date]), RETAIL_PRICE[Valid_from] )
return
maxx(filter(RETAIL_PRICE, RETAIL_PRICE[Supplier] =sales[Supplier] && RETAIL_PRICE[Article] = Sales[Article] && RETAIL_PRICE[Valid_from] =_max), RETAIL_PRICE[Retail Price] )
3 Replies
- amitchandak
Super User
benjaminpichl , I think you need a new column in sales table
New column =
var _max = maxx(filter(RETAIL_PRICE, RETAIL_PRICE[Supplier] =sales[Supplier] && RETAIL_PRICE[Article] = Sales[Article] && RETAIL_PRICE[Valid_from] <= Sales[date]), RETAIL_PRICE[Valid_from] )
return
maxx(filter(RETAIL_PRICE, RETAIL_PRICE[Supplier] =sales[Supplier] && RETAIL_PRICE[Article] = Sales[Article] && RETAIL_PRICE[Valid_from] =_max), RETAIL_PRICE[Retail Price] )
Seems to work nicely in my example. I will try to apply it to my actual data on monday and mark it as solved if it works!
Thanks a lot!!
Hi benjaminpichl,
Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept it to help others who faced similar requirements.
If these also don't help, please share more detailed information to help us clarify your scenario to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
