Need to create a table/visual from two tables filtering the second table

[AlfredGulyas]

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

[ryan_mayu · Accepted solution]

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 1

pls see the attachment below

[AlfredGulyas]

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 · Accepted solution]

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 1

pls see the attachment below

[AlfredGulyas]

Looks like magic to me and works. Thank you very much!

So basically this "empty" measure just filtering out the whole table?

[ryan_mayu · Accepted solution]

measure has been added to the table visual, so it works on that  visual and means filtering the itemmast table.

[ryan_mayu]

could you pls proivde some sample data and expected output?

[AlfredGulyas]

Please see it above