Total stocks calculated by actuel stocks value

[Ronald123]

Hello,

Can someone help my on my way to get a measure for the total stocks on a periode by year?

I need to calculate the total stocks with the total stocks of today from the table stock[totalsupply].

On each date in the table stocktransaction[in/out], the measure must calculate a total supply in the past.

In attachment i have make a test pbix file.

Thanks,

https://drive.google.com/open?id=1zNKK_aJZX16mqqon1tmPJGcc_q1EhtL6
Ronald

[v-shex-msft · Accepted solution]

Hi @Ronald123,

You can use below measure to get the current transaction total:

Transaction Total = 
VAR current_code =
    LASTNONBLANK ( StockTransactions[StockItemCode], [StockItemCode] )
VAR original_total =
    LOOKUPVALUE ( Stock[TotalSupply], Stock[StockItemCode], current_code )
RETURN
    SUMX (
        FILTER (
            ALL ( StockTransactions ),
            [Date] <= MAX ( StockTransactions[Date] )
                && [StockItemCode] = current_code
        ),
        [In / out]
    )
        + original_total

Notice: if your data contains any privacy data, please do mask sensitive data before sharing.

Regards,

Xiaoxin Sheng