DAX – Stock Value with Dynamic Price

funichi88 Power BI

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:

Tab1.png

Price table by product, Warehouse and period

TAb2.png

Data Model

Schema

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

 

Stock:
=CALCULATE(SUM(Movimenti[_Pezzi]);Filter(ALL(Data[Data]);(Data[Data]) <= MAX(Data[Data])))
——————————————————————————————————————————–
Price_Step1:
=CALCULATE(
  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
Price:
=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)
Value:
=SUMX(CALCULATETABLE(Movimenti;Filter(ALL(Data[Data]);(Data[Data]) <= MAX(Data[Data])));[Stock]*CALCULATE([Prezzo];FILTER(ALL(Data);Data[AnnoMese]=MAX(Data[AnnoMese]))))
——————————————————————————————————————————–
Annunci

Rispondi

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

Gravatar
Logo 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 )

Google+ photo

Stai commentando usando il tuo account Google+. ( Chiudi sessione / Modifica )

Annulla

Connessione a %s...