topic Re: Calculating cumulative stock value in DAX Commands and Tips

Calculating cumulative stock value

JR_Lee — Thu, 04 Nov 2021 10:04:54 GMT

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

Re: Calculating cumulative stock value

JR_Lee — Thu, 04 Nov 2021 10:05:35 GMT

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

Re: Calculating cumulative stock value

smpa01 — Thu, 04 Nov 2021 13:37:26 GMT

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

Re: Calculating cumulative stock value

JR_Lee — Thu, 04 Nov 2021 14:38:08 GMT

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.

Re: Calculating cumulative stock value

smpa01 — Thu, 04 Nov 2021 15:17:34 GMT

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

Re: Calculating cumulative stock value

JR_Lee — Thu, 04 Nov 2021 15:38:59 GMT

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.

Re: Calculating cumulative stock value

smpa01 — Thu, 04 Nov 2021 15:48:08 GMT

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

Re: Calculating cumulative stock value

JR_Lee — Fri, 05 Nov 2021 09:30:32 GMT

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?

Re: Calculating cumulative stock value

smpa01 — Fri, 05 Nov 2021 11:50:47 GMT

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

Re: Calculating cumulative stock value

JR_Lee — Fri, 05 Nov 2021 12:09:05 GMT

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

Re: Calculating cumulative stock value

smpa01 — Fri, 05 Nov 2021 13:00:12 GMT

JR_Lee pbix is attached

Re: Calculating cumulative stock value

smpa01 — Fri, 05 Nov 2021 13:11:14 GMT

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

Re: Calculating cumulative stock value

JR_Lee — Mon, 08 Nov 2021 08:20:44 GMT

Thanks smpa01, that's really helpful.

Re: Calculating cumulative stock value

Anonymous — Fri, 07 Jul 2023 08:39:55 GMT

smpa01 Hi SuperUser

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