Forum Discussion

Administrator

2 years ago

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

Anonymous

2 years ago

Hi Syndicate_Admin

Please try the following Dax:

PreviousMonthOpeningBalance =
VAR PreviousMonthBalance = 
    CALCULATE(
        [WarehouseBalance], 
        FILTER(
            ALL(Calendar), 
            Calendar[Date] < MIN(Calendar[Date])
        )
    )
RETURN PreviousMonthBalance

EndingBalance222 =
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.

Anonymous

Not applicable

2 years ago

Hi Syndicate_Admin

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.

Syndicate_Admin

Administrator

2 years ago

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

Anonymous
Not applicable
2 years ago
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 total

Result:

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 total

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cumulative Sum by Line.pbix57 KB
- Syndicate_Admin
  Administrator
  2 years ago
  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 inventario
  Will 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?
  - Anonymous
    Not applicable
    2 years ago
    Hi Syndicate_Admin
    
    Please try the following Dax:
    
    PreviousMonthOpeningBalance = VAR PreviousMonthBalance = CALCULATE( [WarehouseBalance], FILTER( ALL(Calendar), Calendar[Date] < MIN(Calendar[Date]) ) ) RETURN PreviousMonthBalance
    
    EndingBalance222 = 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.

Forum Discussion

Cumulative Sum by Line

Related content

cumulative sum

Cumulative SUM

Cumulative sum for a line chart

CUMULATIVE SUM FOR MULTIPLE LINES

cumulative sum