Forum Discussion

brunofds's avatar
brunofds
Icon for Helper I rankHelper I
6 years ago

DAX - Stock average cost

 

Hey guys. I'm trying to compute some data from the stock market. The process is similar so commom inventory valuation and i've used lot's of insights from DAX PATTERNS website.

 

 

MODEL:

Model (simplified)

 

 

 

DATASET:

Op.datestockqtypricebalance
Buy18/09/2017AMZN300010,513000
Sell29/11/2019AMZN50037,76-500
Buy02/12/2019AMZN50037,5500
Sell04/12/2019AMZN50037,13-500
Buy06/12/2019AMZN50037,38500
Sell15/01/2020AMZN7541,94-75
Sell15/01/2020AMZN40041,94-400
Buy16/01/2020AMZN40042,02400
Buy16/01/2020AMZN7542,175
Sell04/03/2020AMZN300016,75-3000

 

 

 

DESIRED RESULT IN YELLOW: 

 

Desired result

 

For instance, on 02/12/2019, my avg cost was 15.01, because of this operation (excel mode, sorry):

( (500* 37,5) + (2500*10,51) ) / 3000

 

 

**************************************

I've already computed dynamically the total stocks i own given a date, by this formula:

 

UnitsInStock =
IF (
MIN ( 'Date'[Date] )
<= CALCULATE ( MAX ( 'fOperation'[date] ); ALL ( 'fOperation' ) );
CALCULATE (
SUM ( 'fOperation'[balance] );
FILTER ( ALL ( 'Date'[Date] ); 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)
)

 

****************

 

I want to compute dynamically the average cost too, but i'm not achieving that.

 

My inicial measure was :

Avg Price =
VAR FinancialAmount =
CALCULATE (
SUMX ( fOperation; fOperation[price] * fOperation[qty] );
fOperation[Op.] = "buy"
)

VAR UnitsInStock = [UnitsInStock]

RETURN
DIVIDE ( FinancialAmount; UnitsInStock )

 

***

But thats not correct.
For instance, on 02 / dec / 2019, i'd have, based in that measure:

 

 

 

Hope to find some clues here in the community. 

 

Thanks in advance. 

Cheers.

 
 
 
 
 
 
 
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi brunofds ,

     

    Can you explain about the calculate logic? I'm not quite understand the below formula.

    ( (500* 37,5) + (2500*10,51) ) / 3000

     

    Best Regards,

    Jay

     

    Community Support Team _ Jay Wang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • PierreCA's avatar
    PierreCA
    Icon for Resolver I rankResolver I
    3 years ago

    Hello, 

    You may try that code to sort your data and compute new quantities. 
    Bu you gonna have to find a way to overpass the filter effect of the slicer on the calculation  or the result will always be different according the dates chosen. 

    Good luck!

    VAR BuySale = Feuil2[B/S]
    VAR DateEvent = Feuil2[date]
    VAR StockName= Feuil2[StockName]

    VAR AverageValue = IF(
    BuySale ="B",
    Feuil2[TotalStockCost]/Feuil2[QuantiteAtEndTransaction],
    CALCULATE(

    SUMX(Feuil2, Feuil2[TotalStockCost]/Feuil2[QuantiteAtEndTransaction]),
    filter(Feuil2,MAX(Feuil2[date])<DateEven),
    filter(Feuil2,Feuil2[StockName]=StockName)))


    RETURN
    AverageValue