Calculating cumulative stock value

[JR_Lee]

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).

[Anonymous]

@smpa01 Hi SuperUser

I dont have any blank value in my Cumulative measure yet the first value is not picked. Any reason why

[JR_Lee]

Thanks @smpa01, that's really helpful.

[JR_Lee]

Oops, the DAX at the top should be at the very bottom.

[smpa01]

@JR_Lee  any chance you can provide sample data and desired output?

[JR_Lee]

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]

@JR_Lee  what is the expected output based on the sample data that you provided?

[JR_Lee]

Hi @smpa01 
Not too sure exactly as I just typed up the data in my reply as a loose example and my table has 8m rows.
The expected output should be the cumulative result of standard cost x current value of standard cost - I think?
Sorry if this is not clear.

[smpa01]

@JR_Lee  can you take a moment to take a look into what you provided and what it should yield to?

[JR_Lee]

Date	Cumulative Qty	Cumulative Stock Value
02/10/2021	-46	-£1,220.85
03/10/2021	441	£12,191.13
04/10/2021	22	£12,156.67

Hi, so this should be the result of the data I provided above. Does this help?

[smpa01]

@JR_Lee  and how did you reach to this number? Can you describe the logic?

I only tested Cumulative Qty and it does not make sense to me how you reached at -46 for 2021-10-02 while sum of all the quantity for that amounts to -21?

Please clearly describe how did you reach to each of those numbers for each of the day for both Cumulative Qty and Cumulative Stock Value

[JR_Lee]

Sorry, I realised I was summing Qty individually rather than cumulatively.
The below should be correct - the stock value should also be correct.

-21

420

442

[smpa01]

@JR_Lee  pbix is attached

[smpa01 · Accepted solution]

@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] ) )
)