Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hello Team,
I need help creating a new column in a table.
My tables are as follows:
Table Stock_main:
Date | Description | Balance |
28/01/2022 | Sugar | 5.00 |
28/01/2022 | Sugar | 4.00 |
28/01/2022 | Soda | 3.00 |
27/01/2022 | Chicken | 7.00 |
26/01/2022 | Chicken | 5.00 |
Purchasing table:
Date | des | Unit price |
03/01/2022 | Sugar | 10.00 |
01/01/2022 | Sugar | 8.00 |
01/01/2022 | Soda | 15.00 |
01/01/2022 | Chicken | 100.00 |
31/12/2022 | Chicken | 110.00 |
I need to vlookup the latest unit price in the table Stock_main from the purchasing table.
I appreciate your help.
Kind Regards,
Hasvine
Solved! Go to Solution.
@Anonymous , a new column in Stock_main
MAxx(filter(Purchasing, Purchasing[des] =Stock_main[Description] && Purchasing[Date] <=Stock_main[Date]) ,Purchasing[Date] )
Hi @Anonymous ,
It required bit of modelling where you need to create a calender table and join these table with it.
Then create a column as below:-
latest_unit_Price =
VAR _max_date =
CALCULATE (
MAX ( 'Purchasing table'[Date] ),
FILTER (
'Purchasing table',
'Purchasing table'[description] = Stock_main[Description]
)
)
RETURN
CALCULATE (
SELECTEDVALUE ( 'Purchasing table'[Unit price] ),
FILTER ( 'Purchasing table', 'Purchasing table'[Date] = _max_date )
)
Refer a file below:-
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
Hi @Anonymous ,
It required bit of modelling where you need to create a calender table and join these table with it.
Then create a column as below:-
latest_unit_Price =
VAR _max_date =
CALCULATE (
MAX ( 'Purchasing table'[Date] ),
FILTER (
'Purchasing table',
'Purchasing table'[description] = Stock_main[Description]
)
)
RETURN
CALCULATE (
SELECTEDVALUE ( 'Purchasing table'[Unit price] ),
FILTER ( 'Purchasing table', 'Purchasing table'[Date] = _max_date )
)
Refer a file below:-
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
@Anonymous , a new column in Stock_main
MAxx(filter(Purchasing, Purchasing[des] =Stock_main[Description] && Purchasing[Date] <=Stock_main[Date]) ,Purchasing[Date] )
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
90 | |
84 | |
70 | |
49 |
User | Count |
---|---|
143 | |
121 | |
112 | |
59 | |
58 |