Accumulated Balance

[Syndicate_Admin]

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

Saldo_acum_ = CALCULATE(sum(Consulta1[valor]),FILTER(ALLSELECTED(Consulta1),Consulta1[RefDate]<=MAX(Consulta1[RefDate])))

Os agradecería algo de luz

Greetings.

[v-stephen-msft · Accepted solution]

Don't @Syndicate_Admin,

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.

[Syndicate_Admin]

Hello

Attached the file

Pibx file

The result I have is the red marking, the one I need is the marking in green

Thank you

[stevedep]

Something like this?

[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]

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

[stevedep]

Can you share a sample dataset with some scenarios and expected output?