Forum Discussion

Uspace87's avatar
Uspace87
Icon for Resolver III rankResolver III
2 years ago

Data modelling

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. ...
Need Help
Chakravarthy's avatar
Chakravarthy
Icon for Resolver II rankResolver II
2 years ago

 ,  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