How to bring a result from other row on same column. Relative data reference

[rcspaiva]

Hey guys, I'm a newb on Power BI. I'm stuked in a problem almost three days, so I've decided to share it cause I did not find nothing about.

There is a column what has a set of products ID, and a second one what maybe bring the raw material of this product. I want use a kind of relative references in a measure to bring cost of each product, but some products need to use this raw material as reference and some column ID. Someone could help with this problem?

Measure that I'm using to calculate the cost:

P. Cost d-30 =
var d-30 = (LASTDATE(Entry of product'[DateEntry]))-29

return
CALCULATE(sum(Entry of product'[Value Entry])/SUM(Entry of product'[Quantity Entry);Entry of product'[DateEntry]>=d30)

Eg:

Id Product	Raw Material	expected result from the measure "P.Cost d-30"
657	657	100
658	657	100
659	657	100
920		250
400		300
700	400	300
1020		90
2000		90
3000		500
3001	3000	500

How can I improve it to reach my goal?

Thx in advance!

"EDITED: My data tables and they relations with others tables already works perfectly, this measure mentioned above already works well with products without reference to raw material"

[Thyago_Rezende]

Hi,

Where´s value_entry, quantity_entry and date_entry fields?

[rcspaiva]

Hi @Thyago_Rezende thx for reply, sorry forget mention this part.They are in others tables, the table of cost are conected by ID Product to table register of products mentioned below as a EG, date cost products to a table just with dates to be use as a slicer, my measure works well on produtcs without raw material reference, my problem is exclusive with the relative reference of raw material.

Best regards

[Thyago_Rezende]

Did you try to use LookUpValue function? Do you know it?

https://msdn.microsoft.com/en-us/library/gg492170.aspx

[rcspaiva]

How I've tried it:

Raw material cost = LOOKUPVALUE(table [id product];table [raw material];[P. cost d-30])

The result comes blank, or It doesnt worked, or I misundertand how to use it.

(I dont have a column with the expected result , its come from a measure)

[rcspaiva]

Hey guys, I'm reposting because my last post has bugged. I'm stuked on a problem almost three days, so I've decided to share it cause I did not find nothing about.

I'll try be most clear as possible.. .

There is a column what has a set of products ID, and a second one what maybe bring the raw material of this product. I want use a priority reference in measure the "raw material" and after ID product, a kind of relative references in a measure to bring cost of each product. All my tables are connected by ID product, so its ever bring ID Product first when I use a measure. Someone could help with this problem?

Eg:

Id Product	Raw Material	expected result from the measure "P.Cost d-30"
657	657	100
658	657	100
659	657	100
920		250
400		300
700	400	300
1020		90
2000		90
3000		500
3001	3000	500

How can achive this?

Thx in advance!

[rcspaiva]

Hey guys, I'm reposting because my last post has bugged. I'm stuked on a problem almost three days, so I've decided to share it cause I did not find nothing about.

I'll try be most clear as possible.. .

There is a column what has a set of products ID, and a second one what maybe bring the raw material of this product. I want use a priority reference in measure the "raw material" and after ID product, a kind of relative references in a measure to bring cost of each product. All my tables are connected by ID product, so its ever bring ID Product first when I use a measure. Someone could help with this problem?

Eg:

Id Product	Raw Material	expected result from the measure "P.Cost d-30"
657	657	100
658	657	100
659	657	100
920		250
400		300
700	400	300
1020		90
2000		90
3000		500
3001	3000	500

How can achive this?

Thx in advance!