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.

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