Forum Discussion

vinnyrigoni's avatar
vinnyrigoni
New Member
1 year ago
Solved

Sales Pace

Hello, I would like to create a sales rhythm in which I have the YEAR-MONTH values ​​in column 3, while in column 4 I have the accumulated. I would like to replace the values ​​equal to zero in column 3 with the sum of the last accumulated, and thus be able to make column 4 accumulate larger values ​​even if there is still 0 in column 3. Remembering that every month the value will be updated at the end of the month, so it can no longer be the accumulated one, but rather the current closed accumulated one. Could anyone help?

 

accumulated formula:

 

 

 

Accumulated = 
CALCULATE(
    SUM(Table[column2]),
    FILTER(
        ALL(Table),
        Table[Date] <= MAX(Table[Date])
    )
)

 

 

 

Exemplo:

- in line setembro , column 3, the value will be 5.775.824,00. 

- in line outubro, collumn 3, the value will be 5.775.824,00 + 5.775.824,00  = 11.551648

- in line novembro, collumn 3, the value will be 11.551648 + 11.551648 = 23.103.296

.....

..

.

.

.

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi vinnyrigoni 

    You can refer to the pbix file attacted in the end.

    First create an index column in power query.



    Then using the following dax:

    Ending Balance2 = 
    VAR _index = SELECTEDVALUE(Movements[Index])
    VAR total= CALCULATE(SUM(Movements[Mov Existence]),FILTER(ALL(Movements),'Movements'[Index] <= _index))
    
    RETURN total

     

     

    Result:

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi vinnyrigoni 

    You can refer to the pbix file attacted in the end.

    First create an index column in power query.



    Then using the following dax:

    Ending Balance2 = 
    VAR _index = SELECTEDVALUE(Movements[Index])
    VAR total= CALCULATE(SUM(Movements[Mov Existence]),FILTER(ALL(Movements),'Movements'[Index] <= _index))
    
    RETURN total

     

     

    Result:

     

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.