Merge tables using latest dates

[Omega]

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!

[Greg_Deckler]

Are you trying to do this in DAX or M or do you care?

[Omega]

Can you please share both if possible and the difference between both in terms of performance and easiness to implement? Thanks!

[v-jiascu-msft]

Hi, @Omega

If you want to use DAX, this formula will help.

QTYMeasure =

CALCULATE ( VALUES ( Qty[qty] ), LASTDATE ( Mapping[date] ) )

Best Regards

Dale

[Omega]

Instead of getting the QTY from QTY table, how to get item 2 from mapping table? Meaning, I'll have two columns, one that have A and the other have Z.

Thanks!

[Omega]

@v-jiascu-msft 

Hi Dale,

I tried your method but sales are empty. Don't know why 😞 

Please advise. 

[v-jiascu-msft]

@Omega

Hi,

Did this work? What else can I do for you?

Best Regards!

Dale

[v-jiascu-msft · Accepted solution]

@Omega

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] ) )

[Omega]

Thanks for the reply. I'll try to implement and let you know if I'm having challenges. 🙂