Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have the following two tables:
Table A
product cost startdate enddate
A 100 01.01.2019 31.03.2019
A 150 01.04.2019 ......
Table B
product date number of products
A 01.02.2019 1
A 01.05.2019 2
I would like to calculate the value of cost from table A in table B
So it should look like this:
product date number of products cost Cost of goods
A 01.02.2019 1 100 1*100 = 100
A 01.05.2019 2 150 2*150 = 300
Solved! Go to Solution.
Hi @Anonymous
Please try the below.
Column = CALCULATE( MAX('Table A'[Cost]), FILTER( RELATEDTABLE('Table A'), 'Table A'[startDate] <= 'Table B'[date] ) )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please try this:
Hi @Anonymous
You can do it in Query Editor like below
Price table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSBob6QGRkYGgJ5BgDOcYQTqwOVJUpTJUJTJVSbCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Product Code" = _t, Cost = _t, startDate = _t, endDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product Code", type text}, {"Cost", Int64.Type}, {"startDate", type date}, {"endDate", type date}}) in #"Changed Type"
Quantity table
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw0jcyMLQEcgyVYnUQwqYwYSMUYTMk1bEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, date = _t, #"number of Products" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"date", type date}, {"number of Products", Int64.Type}}), #"Added Custom" =Table.AddColumn(#"Changed Type", "Cost", each let d = [date] in Table.SelectRows(ProductPrice, each [startDate] <= d and (if [endDate] <> null then [endDate] >= d else true))[Cost]), #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Cost"), #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Cost", type number}}) in #"Changed Type1"
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Mariusz thx for your reply
Looks pretty complicate, is there a way to do it with dax/calculated columns?
Please try this:
Hi @Anonymous
Please try the below.
Column = CALCULATE( MAX('Table A'[Cost]), FILTER( RELATEDTABLE('Table A'), 'Table A'[startDate] <= 'Table B'[date] ) )
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Create a Measure in First table as given below.
Measue = sum(Table1[Cost])
Later create the Calculated column in Second table as given below.
Cost of Goods= Table1[Number of Probucts]*Measure
Please make sure the correct relationships between the two tables.
Thanks,
Hemanth Thota.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
66 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |