Forum Discussion
Accumulated Balance
Because the date is not unique, you cannot use the Date column for accrual in the FILTER function. You must add an index column in Power Query.
Before you add an index column, you must ensure that the date column is sorted from small to large and that the Balance column is also sorted from small to large, so that the result is as desired.
Tips: I cannot enter the PBIX file that you provided to add index columns in Power Query, so I exported the data and recreated a PBIX file.
So the measure is
Saldo_acum = CALCULATE(SUM('Sheet1'[Saldo]),FILTER(ALLEXCEPT('Sheet1',Sheet1[Account],Sheet1[BPLId]),[Index]<=MAX('Sheet1'[Index])))You can check more details from here.
Best regards
Stephen Tao
If this post helps,then consider Accepting it as the solution to help other members find it faster.
Thank you for responding
It's not exactly what I'm looking for.
I'll explain
I have a calculated column that is the value (debit-credit) - balance
Here's the report
As you can see there are several notes that are with the same Refdate '31/01/20', the same TransId 7313 and different Line_Id
The column saldo_acum is the formula I've put up
Saldo_acum_ = CALCULATE(sum(Consulta1[valor]),FILTER(ALLSELECTED(Consulta1),Consulta1[RefDate]<=MAX(Consulta1[RefDate])))
What I want is that instead of joining by date, I make the cumulative line by line by transid and line_id
This result is filtered by Account, so it has to be used for all filters
Thanks a lot
Can you share a sample dataset with some scenarios and expected output?