Forum Discussion
AdministratorAccumulated Balance
Hello
I'm trying to calculate a balance that gives me the summation of an account in a period,
The fields I have are the transid (seat number), the line_id (line number), what I'm looking for is to create a measure that calculates the accumulated balance line by line, that is, what marks the Acum column.
| TransId | Line_ID | Account | Nivel4 | RefDate | Debit | Credit | Balance | Now |
| 7297 | 2 | 30000800 | 3000 | 01/01/2020 0:00 | 1624.31 | 0,00 € | 1.624,31 | 1.624,31 |
| 984 | 3 | 30000800 | 3000 | 31/01/2020 0:00 | 1624.31 | 0,00 € | 1.624,31 | 3.248,62 |
| 7313 | 10 | 30000800 | 3000 | 31/01/2020 0:00 | 0,00 € | 0.45 | - 0,45 | 3.248,17 |
| 7313 | 4 | 30000800 | 3000 | 31/01/2020 0:00 | 0,00 € | 1.23 | - 1,23 | 3.246,94 |
| 7313 | 6 | 30000800 | 3000 | 31/01/2020 0:00 | 0,00 € | 1.36 | - 1,36 | 3.245,58 |
| 7313 | 8 | 30000800 | 3000 | 31/01/2020 0:00 | 0,00 € | 1.81 | - 1,81 | 3.243,77 |
| 7307 | 0 | 30000800 | 3000 | 31/01/2020 0:00 | 0,00 € | 1619.46 | - 1.619,46 | 1.624,31 |
| 5720 | 1 | 30000800 | 3000 | 14/02/2020 0:00 | 913.32 | 0,00 € | 913,32 | 2.537,63 |
| 5666 | 1 | 30000800 | 3000 | 24/02/2020 0:00 | 475.2 | 0,00 € | 475,20 | 3.012,83 |
| 8505 | 5 | 30000800 | 3000 | 29/02/2020 0:00 | 1309.67 | 0,00 € | 1.309,67 | 4.322,50 |
| 8493 | 9 | 30000800 | 3000 | 29/02/2020 0:00 | 9.3 | 0,00 € | 9,30 | 4.331,80 |
I've tried this formula but I've got all the same-day notes
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.
6 Replies
- stevedep
Memorable Member
Something like this?
Syndicate_AdminThank 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_IdThe 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
- stevedep
Can you share a sample dataset with some scenarios and expected output?
- Syndicate_Admin
Hey.
What if there is not only one booking per day per account? Does it consider the order in the table or do I have to insert an index and add that to the formular? If the last is correct, could you please tell me how you would solve it?!
- Syndicate_Admin
Hello
Attached the file
The result I have is the red marking, the one I need is the marking in green
Thank you
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.
