Data modelling

[Uspace87]

Hi all,

I n my model I got a "Date Table", "Product Table", a fact table with "Sold qty" by date and by product and then I got anothe table with all the "Product Unit cost" by product, by date.

I would like to find the best way to calculate "Sold Qty * Unit cost" over time. 

What's the best solution in terms of data modelling/ dax?

thank you everyone.

[Chakravarthy]

 ,  If continuous dates are available, then you can merge tables in Power Query (Select multiple columns by pressing ctrl) and  get cost column in fact. You can do the same by below columns in Fact Table. 

New column = 
maxx(filter(Price , Fact[product id] = Price[product id] &&  Fact[Date] = Price[Date]), Price[Price])

(OR)

New column = 
var _1 = maxx(filter(Price , Fact[product id] = Price[product id] &&  Fact[Date] <= Price[Date]), Price[Price]) 
return 
maxx(filter(Price , Fact[product id] = Price[product id] &&  Fact[Date] = _1 ), Price[Price])

@Uspace87

[Idrissshatila]

Hello @Uspace87 ,

in the data modeling you will be having the date and product tables linked to both facts, and calculate a dax measure to see sold qty from fact table * the unit sold from the second fact table.

you then display the measure  with respect to the product from the product table.