cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 Employee

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 Employee

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 Employee

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.