Forum Discussion
Solved
Cumulative Sum by Line
Hello, we have a table where all the movements are by item/date/types of movement and we need to calculate the accumulated stock to later calculate the average cost. This is the formula we have: ...
Hi Syndicate_Admin
Please try the following Dax:PreviousMonthOpeningBalance = VAR PreviousMonthBalance = CALCULATE( [WarehouseBalance], FILTER( ALL(Calendar), Calendar[Date] < MIN(Calendar[Date]) ) ) RETURN PreviousMonthBalanceEndingBalance222 = VAR _index = SELECTEDVALUE(Movements[Last Change]) VAR _artical = SELECTEDVALUE(Movements[Item]) VAR PreviousMonthBalance = [PreviousMonthOpeningBalance] VAR Total = CALCULATE( [WarehouseBalance], FILTER( ALL(Movements), Movements[Last Change] <= _index && Movements[Item] = _artical ) ) RETURN IF( _index = MINX(ALL(Movements), Movements[Last Change]), PreviousMonthBalance + Total, Total )
If the above dax can't help you solve your problem, can you provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please provide sample data that fully covers your issue(in the form of pbix file or table) and the expected outcome based on the sample data you provided.So that I can help you better. Please remove any sensitive data in advance.
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Article Mov LastChange Mov Existence ID DateIssue 01 5930 2.1 Invoice 04/06/2024 16:45 -20 184214 04/06/2024 01 5930 2.1 Invoice 06/06/2024 15:49 -40 184195 06/06/2024 01 5930 5.3 Miscellaneous Input 10/07/2024 14:36 40 105465 10/07/2024 01 5930 2.1 Invoice 10/07/2024 14:38 -60 185178 10/07/2024 01 5930 3.2 Import Entry 30/07/2024 13:00 980 26403 30/07/2024 01 5930 5.2 Transfer 30/07/2024 14:25 -20 105849 30/07/2024 01 5930 5.2 Transfer 30/07/2024 14:25 20 105849 30/07/2024 01 5930 3.2 Import Entry 30/07/2024 15:36 1400 26405 30/07/2024 01 5930 2.1 Invoice 02/08/2024 13:43 -100 185864 02/08/2024 01 5930 2.1 Invoice 05/08/2024 16:24 -80 185917 05/08/2024 01 5930 2.1 Invoice 06/08/2024 14:55 -160 185934 06/08/2024 01 5930 5.2 Transfer 06/08/2024 16:39 -200 105981 06/08/2024 01 5930 5.2 Transfer 06/08/2024 16:39 200 105981 06/08/2024 01 5930 2.1 Invoice 07/08/2024 15:00 -200 185968 07/08/2024 02 0092 2.1 Invoice 03/06/2024 09:08 -360 184129 03/06/2024 02 0092 2.1 Invoice 03/06/2024 09:08 -640 184129 03/06/2024 02 0092 2.1 Invoice 03/06/2024 13:14 -60 184125 03/06/2024 02 0092 5.1 Material consumption 04/06/2024 09:20 -205.5 104890 04/06/2024 02 0092 2.1 Invoice 05/06/2024 15:07 -1500 184174 05/06/2024 02 0092 2.1 Invoice 06/06/2024 13:57 -600 184251 06/06/2024 02 0092 2.1 Invoice 06/06/2024 14:27 -1000 184249 06/06/2024 02 0092 5.1 Material consumption 07/06/2024 10:01 -320.6 104976 07/06/2024 02 0092 2.1 Invoice 07/06/2024 10:57 -1640 184319 07/06/2024 02 0092 2.1 Invoice 07/06/2024 11:04 -2000 184322 07/06/2024 02 0092 2.1 Invoice 01/07/2024 09:17 -1000 184889 01/07/2024 02 0092 5.2 Transfer 01/07/2024 14:43 -20 105323 01/07/2024 02 0092 5.2 Transfer 01/07/2024 14:43 -100 105323 01/07/2024 02 0092 2.1 Invoice 02/07/2024 11:03 -2000 184939 02/07/2024 02 0092 3.2 Import Entry 02/07/2024 16:23 5600 26322 02/07/2024 02 0092 3.2 Import Entry 02/07/2024 16:23 15400 26322 02/07/2024 02 0092 2.1 Invoice 01/08/2024 11:31 -1000 185791 01/08/2024 02 0092 2.1 Invoice 01/08/2024 14:08 -800 185795 01/08/2024 02 0092 2.1 Invoice 01/08/2024 14:13 -1000 185810 01/08/2024 02 0092 5.4 Miscellaneous Output 01/08/2024 14:15 -20 105937 01/08/2024 02 0092 2.1 Invoice 02/08/2024 13:25 -1000 185861 02/08/2024 02 0092 2.1 Invoice 05/08/2024 11:50 -3500 185869 05/08/2024 02 0092 2.1 Invoice 05/08/2024 16:15 -3000 185877 05/08/2024 02 0092 3.2 Import Entry 06/08/2024 11:55 2800 26432 06/08/2024 02 0092 3.2 Import Entry 06/08/2024 11:55 7000 26432 06/08/2024 02 0092 2.1 Invoice 06/08/2024 13:14 -1500 185940 06/08/2024 02 0092 5.2 Transfer 06/08/2024 14:16 -4200 105980 05/08/2024 02 0092 5.2 Transfer 06/08/2024 14:16 4200 105980 05/08/2024 02 0092 2.1 Invoice 06/08/2024 15:03 -100 185900 06/08/2024 02 0092 2.1 Invoice 06/08/2024 15:10 -2000 185913 06/08/2024 02 0092 2.1 Invoice 07/08/2024 13:18 -2000 185964 07/08/2024 02 0092 5.1 Material consumption 14/08/2024 10:11 -685 106075 14/08/2024 02 0092 5.1 Material consumption 14/08/2024 15:10 -14767.5 106105 14/08/2024 02 0092 5.1 Material consumption 14/08/2024 15:10 -527.5 106105 14/08/2024 02 0092 2.1 Invoice 16/08/2024 11:46 -1500 186203 16/08/2024 02 0092 5.2 Transfer 16/08/2024 17:04 -20 106160 16/08/2024 02 0092 5.2 Transfer 16/08/2024 17:04 20 106160 16/08/2024 02 0092 2.1 Invoice 19/08/2024 14:15 -400 186246 19/08/2024 02 0092 2.1 Invoice 19/08/2024 14:43 -40 186249 19/08/2024 Hi Syndicate_Admin
First, create an index column in power query:Then, you can try the following dax to get the result you want:
Ending Balance2 = VAR _index = SELECTEDVALUE(Movements[Index]) VAR total= CALCULATE(SUM(Movements[Mov Existence]),FILTER(ALL(Movements),'Movements'[Index] <= _index)) RETURN totalResult:
If you want to categorize by article, you can try the following dax:
Ending Balance22 = VAR _index = SELECTEDVALUE(Movements[Index]) VAR _artical = SELECTEDVALUE(Movements[Article]) VAR total= CALCULATE(SUM(Movements[Mov Existence]),FILTER(ALL(Movements),'Movements'[Index] <= _index && 'Movements'[Article] = _artical)) RETURN totalBest Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much for your support, I took the information you sent as a basis and it worked 🙂 . I only made a modification in the formula of Ending Balance 22 so that it would take the value you selected in the segmenter.
_Ending Balance222 =VAR _index = SELECTEDVALUE(Movements[Last Change])VAR _artical = SELECTEDVALUE(Movements[Item])Total VAR= CALCULATE([WarehouseBalance],FILTER(ALL(Movements),Movements[Last Change] <= _index && Movements[Item] = _artical))Total RETURN
An additional detail, as you can see in the image when selecting the month of July, the initial balance for that item is 160 (which is the end of the month of June).... with the formula _Ending Balance222, If the values are correct because it calculates over the entire table, the detail is that the process was very very slow.
The formula with which the Balance of the previous month is calculated is as follows:Previous Month Opening Balance =var inventory = CALCULATE([WarehouseBalance], FILTER(ALL(Calendar), Calendar[Date]<MIN(Calendar[Date])))RETURN inventarioWill it be possible to put the result of this last formula as a constant value in the Ending Balance 222 formula, so that only the balance of the previous month is added in the first line and from the second line onwards it continues with the accumulated balance in a normal way?
