Forum Discussion

Uspace87's avatar
Uspace87
Resolver 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.

 

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.

Need Help

2 Replies

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    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.

  • Chakravarthy's avatar
    Chakravarthy
    Resolver 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