Today I will describe a simple solution to a problem I had to solve lately:
Enhancement of warehouse stock (quantity) using as price, that associated to a particular Item in a given period of time (selected by the user). In the table of Item a price is valid for a certain time period (date range) while the amount of stock is obviously appreciated moment by moment.
Start Situation:
Stock Movement table:
Price table by product, Warehouse and period
Data Model
At the interface, the user needs to select a date (the dim time). The model goes to look for the price for the valuation corresponding to the selected date, or within the date range. This price will be multiplied by the amount (line by line) without taking into consideration the date of the stock with respect to the price.
NB: in essence we have two fact tables (movements and prices) of which the second table can not be linked to the dimension of time because we do not give but a range.
DAX