DAX – Stock Value with Dynamic Price

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.



=CALCULATE(SUM(Movimenti[_Pezzi]);Filter(ALL(Data[Data]);(Data[Data]) <= MAX(Data[Data])))
  SUM( Prezzi[_Prezzo] );
FILTER( ALL( Prezzi[Magazzino] );
CONTAINS( VALUES( Magazzino[Fisico] ); Magazzino[Fisico]; Prezzi[Magazzino] ) )
——————————————————————————————————————————–the price enhancement with date within the range of dates found in the price table
=CALCULATE([Prezzo_Step1];FILTER(Prezzi;Prezzi[DataDa]<=MAX(Data[AnnoMese]) && Prezzi[DataA]>= MIN(Data[AnnoMese])))
Inventories. In the second part of the formula shall select MAX yearMonth (an integer)
=SUMX(CALCULATETABLE(Movimenti;Filter(ALL(Data[Data]);(Data[Data]) <= MAX(Data[Data])));[Stock]*CALCULATE([Prezzo];FILTER(ALL(Data);Data[AnnoMese]=MAX(Data[AnnoMese]))))


Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo di WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione /  Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione /  Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione /  Modifica )

Connessione a %s...