Sales Pace

[vinnyrigoni]

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 · Accepted solution]

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.