cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pelucapampa Regular Visitor
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
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] )
    )
)

1.PNG

 

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
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".

How to Get Your Question Answered Quickly

 

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.
pelucapampa Regular Visitor
Regular Visitor

Re: Calculate balance of account with accunt transactions table

Ok, sorry.

 

Data table

 

CODIGOASIENTOCUENTAFECHAMODIFICACION MONTOREAL 
16795610112609/01/2019 $               1.440,00
16795210112609/01/2019 $               7.439,35
16783310112608/01/2019 $                     80,00
16779610112608/01/2019 $                       0,39
16765010112607/01/2019-$             33.166,05
16764710112607/01/2019-$          123.196,01
16764510112607/01/2019-$             47.972,05
16763810112606/01/2019 $                   170,00
16763010112606/01/2019-$                  350,00
16762910112606/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

Cuenta06/01/201907/01/201908/01/201909/01/2019
101126-$               179,99-$       204.514,10-$       204.433,71-$       195.554,36

 

 

mattbrice Senior Member
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
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.

pelucapampa Regular Visitor
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
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] )
    )
)

1.PNG

 

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.