topic Re: Reverse cumulative sum of Stock Value in DAX Commands and Tips

Reverse cumulative sum of Stock Value

Mat_at_PowerBI — Fri, 14 Feb 2025 11:27:30 GMT

Hi,

I want to calculate the stock value per date. Below a sample of the data, on Today I have the total stock value. I want to use the value changes transactions to calculate the stock on a certain date, by taking the value of today and subtracting the sum of all the CurrentValue changes of today until the date in the date column.

Date	Stock Value	CurrentValue
1-2-2025	180.418,00
2-2-2025	180.418,00	24.629,00
3-2-2025	205.047,00	-102.734,00
4-2-2025	102.313,00	237.327,00
5-2-2025	339.640,00	266.362,00
6-2-2025	606.002,00	311.197,00
7-2-2025	917.199,00	85.538,00
8-2-2025	1.002.737,00
9-2-2025	1.002.737,00	-358,00
10-2-2025	1.002.379,00	-2.379,00
11-2-2025	1.000.000,00

Edit: Based on the first comment, I feel like I did not explain the situation properly. So I added more information below.

This is the Model view of my data. The "Economic Stock" table contains the stock value at this moment with date, in the column "VALUE_ON_HAND". In the "Inventory Transactions" are the movements of the stock.

Below is a sample of the Inventory transactions (640.000 rows) of different articles, I have removed the part ID and CurrentValue for privacy purposes. The CurrentValue represents the monetary value of the transaction.

The table of Economic Stock and Inventory Transactions do not contain every date.

Re: Reverse cumulative sum of Stock Value

Anonymous — Wed, 12 Feb 2025 02:13:13 GMT

Hi, Mat_at_PowerBI

I am glad to help you.

To calculate Stock value for each date, you can refer to my steps:

First create a calculated column to calculate the reverse cumulative sum:

ReverseCumulativeSum = CALCULATE( SUM('Table'[CurrentValue]), FILTER( 'Table', 'Table'[Date] >= EARLIER('Table'[Date]) ) )

Create another calculated column to calculate the Stock value for each date:

CalculatedStockValue = MAXX( FILTER('Table', 'Table'[Date] = MAX('Table'[Date])), 'Table'[Stock Value] ) - 'Table'[ReverseCumulativeSum]

Output:

I have attached the pbix file for this example below, hope it helps you.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Reverse cumulative sum of Stock Value

Mat_at_PowerBI — Thu, 13 Feb 2025 12:01:28 GMT

Dear Fen Ling,

Thank you for answering, I am afraid I did not explain the situation properly. I have edited my original comment. I don't have the necessary PowerBI knowledge to apply your concept to my data, so I hope you can help me further knowing more about the situation.