Forum Discussion
Need to create a table/visual from two tables filtering the second table
I have 2 tables, DailyStock is storing data for every day with the ItemID and with the ActualDate column. The other table ItemMast has the ItemID, the Price and the LastRevisionDate columns. For one item belong multiple revision dates with the corresponding prices so the LOOKUPVALUE gives me always multiple results (not a single Price).
I'd like to create a visual or a table where I can select any date with a slicer (which is done) and will show me the actual price for that date.
I tried some ways where the actual date > latest revison date but no luck.
Obviously I'm a beginner in the Power BI...
I created a data table and create a masure
Measure = if(maxx(FILTER(all(ItemMast),'ItemMast'[Last Revsion Date]<=max('date'[Date])),ItemMast[Last Revsion Date])=max('ItemMast'[Last Revsion Date]),1)add this measure to table visual and set to 1pls see the attachment belowmeasure has been added to the table visual, so it works on that visual and means filtering the itemmast table.
6 Replies
ryan_mayuSuper User
could you pls proivde some sample data and expected output?
Please see it above
DailyStock (contains the stock data for every day of the year)
ItemId Qty ActualDate 10001 10 11/20/2024 10002 5 11/20/2024 10003 15 11/20/2024 10004 80 11/20/2024 10005 50 11/20/2024 10006 215 11/20/2024 10007 6 11/20/2024 10008 56 11/20/2024 10001 5 11/29/2024 10002 3 11/29/2024 10003 15 11/29/2024 10004 26 11/29/2024 10005 42 11/29/2024 10006 152 11/29/2024 10007 5 11/29/2024 10008 55 11/29/2024 ItemMast (contains the details for the Items with every revisions, different items have different revision dates)
ItemId Price Vendor ID Last Revsion Date 10001 10.25 80001 1/15/2024 10001 10.88 80001 3/2/2024 10001 12.12 80002 9/4/2024 10001 12.25 80002 11/28/2024 Selected date: 11/20/2024 and the expected result (only for item 10001):
ItemID Price Vendor ID 10001 12.12 80002 - ryan_mayu
I created a data table and create a masure
Measure = if(maxx(FILTER(all(ItemMast),'ItemMast'[Last Revsion Date]<=max('date'[Date])),ItemMast[Last Revsion Date])=max('ItemMast'[Last Revsion Date]),1)add this measure to table visual and set to 1pls see the attachment belowLooks like magic to me and works. Thank you very much!
So basically this "empty" measure just filtering out the whole table?
