Forum Discussion
Merge tables using latest dates
Dears,
I am new to BI and I have a small challenge:
I have 3 tables: Sales, QTY and Mapping:
Sales: QTY: Mapping:
Item| Sales Item| QTY Item| Item2| Date
A | 50 D | 53 A | D | 27/5/2017
B | 71 E | 67 A | Z | 29/5/2017
C | 85 Z | 70 A | X | 26/5/2017
X | 100
Action: I'd like to create a new table that takes items from sales and get the corresponding QTY from the QTY table based on latest mapping in the Mapping table. For example, A sales = 50 but QTY = 70 = Z because Z have the latest mapping of A.
How to implement such table?
Thanks!
Hi,
Maybe you didn’t create relationships among the tables. That could be why the sales is empty. Do the relationships look like this below?
If so, you can use these two measures to get “item2” and “sales”.
Item2Measure =
CALCULATE ( VALUES ( Mapping[item2] ), LASTDATE ( Mapping[date] ) )
QTYMeasure =
CALCULATE ( VALUES ( Qty[qty] ), LASTDATE ( Mapping[date] ) )
8 Replies
Are you trying to do this in DAX or M or do you care?
Can you please share both if possible and the difference between both in terms of performance and easiness to implement? Thanks!
Hi, Omega
If you want to use DAX, this formula will help.
QTYMeasure =
CALCULATE ( VALUES ( Qty[qty] ), LASTDATE ( Mapping[date] ) )
Best Regards
Dale
