Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
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's avatar
    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's avatar
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.

Syndicate_Admin's avatar
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's avatar
    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.

    • Syndicate_Admin's avatar
      Syndicate_Admin
      Administrator
      2 years ago

      Good day, thank you very much, if it worked for me. 👍