Forum Discussion
Calculating cumulative stock value
StockValue = CALCULATE(
SUM ('Ledger Entry'[Quantity] * 'Ledger Entry'[Current Value] )
FILTER (ALL(Date), Date[Date] <= MAX('Date'[Date])))Hi,
I have a data set where I need to calculate the warehouse cumulative stock levels and the valuation of this stock.
I started by creating a date table and making a relationship between the data table and the picking date.
I then created a DAX formula to calculate cumulative stock levels (which works):
Cumulative Total =
CALCULATE (
SUM ( 'Ledger Entry'[Quantity] ),
FILTER ( ALL ( 'Date'[Date] ), ( 'Date'[Date] ) <= MAX ( 'Date'[Date] ) )
)
Then I made a relationship between the item SKU's
And then created a calculated column on the Ledger Entry table to obtain the current value:
Current Value =
LOOKUPVALUE ( 'Item'[Standard Cost], 'Item'[No_], 'Ledger Entry'[Item No_] )
I'm not understanding exactly what I'm doing at this point as I found the solution on Google.
I use the following DAX to try and calculate the cumulative stock value but it's giving me syntax errors (not really sure if this solution would even be correct).
JR_Lee and if you don't want to create dimension tables
cumulativeQuantity = CALCULATE ( SUM ( t2[Quantity] ), FILTER ( ALL ( t2 ), t2[Posting Date] <= MAX ( t2[Posting Date] ) ) ) _Cumulative Stock Value = CALCULATE ( SUMX ( t1, t1[Standard Cost] * related(t2[Quantity]) ), FILTER ( ALL ( t2), t2[Posting Date] <= MAX ( t2[Posting Date] ) ) )
13 Replies
Oops, the DAX at the top should be at the very bottom.
My data consists of the below (plus the added calculated column, and my date table)
Item Table:No_ Standard Cost 002108 33.04 002108SH 36.52 012117362 37.91 012117363 37.91 012117364 27.48 012117365 50.43 012117366 19.13 012117367 24 012117368 11.48 012117369 5.9
Ledger Entry table:Item No_ Posting Date Quantity 002108 2/10/2021 158 002108SH 2/10/2021 -248 012117362 2/10/2021 -3 012117363 2/10/2021 72 012117364 3/10/2021
311 012117365 3/10/2021 76 012117366 3/10/2021 54 012117367 4/10/2021 -10 012117368 4/10/2021 3 012117369 4/10/2021 29 Expected result should be like:
I can get the cumulative quantity working just not the calculation of the stock value.
Thanks.
smpa01 Hi SuperUser
I dont have any blank value in my Cumulative measure yet the first value is not picked. Any reason why
