cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I

## 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
Microsoft

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
Microsoft

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.
Helper I

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

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Microsoft

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.
Solution Sage

With info provided, all you need is a measure:

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors