cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Calculate balance of account with accunt transactions table

Hi all, i need some help with this case.

I have 3 tables:

DimDateTable

Account Transactions Table

Account Table

I design my report with Matrix:

In rows, Account Number

In Columns Date

And need to calculate account balance for Values.

Set up a DateSlicer to Column matrix

I need to calculate de balance of each account for every date but into account transactions i have all the transactions for all dates.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Calculate balance of account with accunt transactions table

Hi @pelucapampa,

Please new a measure like this:

```Account Total =
CALCULATE (
SUM ( 'Table'[ MONTOREAL ] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CUENTA] ),
'Table'[FECHAMODIFICACION] <= MAX ( 'Table'[FECHAMODIFICACION] )
)
)
```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 REPLIES 6
Community Support Team

## Re: Calculate balance of account with accunt transactions table

Hi @pelucapampa,

Please provide some dummy data and show us your desired output, as suggested in below link. Especially, please describe more about "I need to calculate de balance of each account for every date".

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Calculate balance of account with accunt transactions table

Ok, sorry.

Data table

 CODIGOASIENTO CUENTA FECHAMODIFICACION MONTOREAL 167956 101126 09/01/2019 \$               1.440,00 167952 101126 09/01/2019 \$               7.439,35 167833 101126 08/01/2019 \$                     80,00 167796 101126 08/01/2019 \$                       0,39 167650 101126 07/01/2019 -\$             33.166,05 167647 101126 07/01/2019 -\$          123.196,01 167645 101126 07/01/2019 -\$             47.972,05 167638 101126 06/01/2019 \$                   170,00 167630 101126 06/01/2019 -\$                  350,00 167629 101126 06/01/2019 \$                       0,01

And I need insert into a Matrix Table with calculate the balance for every day, for example:

Balance 06/01/2019 = (0,01 - 350 - 170)

Balance 07/01/2019 = (Balance 06/01/2019) - 47972,05 - 123.196,01 - 33.166,05)

...

Example

 Cuenta 06/01/2019 07/01/2019 08/01/2019 09/01/2019 101126 -\$               179,99 -\$       204.514,10 -\$       204.433,71 -\$       195.554,36

Senior Member

## Re: Calculate balance of account with accunt transactions table

With info provided, all you need is a measure:

`Account Total = SUM ( Table[MONOTOREAL] )`
Super User

## Re: Calculate balance of account with accunt transactions table

Hi,

Build a Calendar Table and create a relationship from the FECHAMODIFICACION column of the Data Table to the Date column of the Calendar Table.  In the Matrix visual, drag the first 2 fields in the Row labels and the Date column from the Calendar Table to the column labels.  Enter this measure

=SUM(Data[MONTOREAL])

Hope this helps.

Regular Visitor

## Re: Calculate balance of account with accunt transactions table

Thanks for your reply, but this measure sum the lines for the date, I need to sum all lines include this date, because the account balance contain the summary.

Community Support Team

## Re: Calculate balance of account with accunt transactions table

Hi @pelucapampa,

Please new a measure like this:

```Account Total =
CALCULATE (
SUM ( 'Table'[ MONTOREAL ] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[CUENTA] ),
'Table'[FECHAMODIFICACION] <= MAX ( 'Table'[FECHAMODIFICACION] )
)
)
```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.