Accumulated Balances

I need to calculate the final balance at 01-07 and the initial balance at 02-07 and its final balance, and so on. The situation is that I cannot find the query because I always reference in the initial balance to the final sado of the previous row, which to its see reference to the initial balance to 01-07 that I have as data. "Nice place to stay"

The consultation should give the following result

Date	Nro account	Incial Balance	Debt	Credit	Balance End
01-07	12345	100	50	20	70
02-07	12345	70	10	30	90

Table 1: Initial Balance

Date	Count No.	Incial Balance
01-07	12345	100

Table2: Bank Statement

Date	Nro account	Debits	Credits
01-07	12345	25	20
01-07	12345	25
02-07	12345	10	30

Anonymous

5 years ago

Hi Syndicate_Admin ,

Sorry for my late reply,now I have figured out the logic:

If the incial balance of the same day could not be found in the incial balance table,then get the value of previous day.

Please try this:

Incial Balance =
VAR _inci =
    CALCULATE (
        MAX ( 'Incial Balance'[Incial Balance] ),
        FILTER (
            'Bank Statement',
            'Bank Statement'[Date] = MAX ( 'Incial Balance'[Date] )
                && 'Bank Statement'[Nro account] = MAX ( 'Incial Balance'[Count No.] )
        )
    )
VAR _pre =
    CALCULATE (
        MAX ( 'Incial Balance'[Incial Balance] ),
        FILTER (
            ALL ( 'Incial Balance' ),
            'Incial Balance'[Count No.] = MAX ( 'Incial Balance'[Count No.] )
                && 'Incial Balance'[Date]
                    = MAX ( 'Bank Statement'[Date] ) - 1
        )
    )
VAR cre_deb =
    CALCULATE (
        SUM ( 'Bank Statement'[Credits] ) - SUM ( 'Bank Statement'[Debits] ),
        FILTER (
            ALL ( 'Bank Statement' ),
            'Bank Statement'[Date]
                = MAX ( 'Bank Statement'[Date] ) - 1
        )
    )
RETURN
    IF ( _inci <> BLANK (), _inci, _pre ) + cre_deb

Or use LASTNOTBLANK() ,it's an easy and effective method:

Incial Balance2 =
VAR _last =
    LASTNONBLANK ( 'Incial Balance'[Incial Balance], [Incial Balance] )
VAR cre_deb =
    CALCULATE (
        SUM ( 'Bank Statement'[Credits] ) - SUM ( 'Bank Statement'[Debits] ),
        FILTER (
            ALL ( 'Bank Statement' ),
            'Bank Statement'[Date]
                = MAX ( 'Bank Statement'[Date] ) - 1
        )
    )
RETURN
    _last + cre_deb

Ending Balance =
[Incial Balance] + SUM ( 'Bank Statement'[Credits] )
    - SUM ( 'Bank Statement'[Debits] )

The final output is shown below:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

Anonymous
Not applicable
5 years ago
Hi Syndicate_Admin ,

Please try the following formula:
Incial Balance = VAR _incial = MAX ( 'Initial Balance'[Incial Balance] ) VAR _sum = CALCULATE ( SUM ( 'Bank Statement'[Debits] ) + SUM ( 'Bank Statement'[Credits] ), FILTER ( ALL ( 'Bank Statement' ), 'Bank Statement'[Date] IN ALL ( 'Initial Balance'[Date] ) ) ) RETURN IF ( MAX ( 'Bank Statement'[Date] ) IN ALL ( 'Initial Balance'[Date] ), _incial, _sum )
The output is shown below:

And actually I'm confused about how to achieve the Balance End column... So could you please explain to me in more detail by providing me with a calculation formula? Thanks😀

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Syndicate_Admin
Administrator
5 years ago
Good morning, first and foremosto thank you very much. unfortunately it didn't work.
Ending Balance - Initial Balance + Credits - Debits
Initial Balance = Ending Balance del dia anterior.

The situation is that I only have the Initial balance at 01-07-2020, that's all. It's my starting point. Perhaps a final Ending balance can be assumed, in this case at 02-07, and from there evaluate the Initial balance at 02-07, and then, at 01-07. i don't known

The table you named as Expected Table, it was just an example, it doesnt exist at all. Solo tengo 3 tablas
Bank statement; Initial Balance; calendar

Anonymous

Not applicable

5 years ago

Hi Syndicate_Admin ,

Sorry for my late reply,now I have figured out the logic:

If the incial balance of the same day could not be found in the incial balance table,then get the value of previous day.

Please try this:

Incial Balance =
VAR _inci =
    CALCULATE (
        MAX ( 'Incial Balance'[Incial Balance] ),
        FILTER (
            'Bank Statement',
            'Bank Statement'[Date] = MAX ( 'Incial Balance'[Date] )
                && 'Bank Statement'[Nro account] = MAX ( 'Incial Balance'[Count No.] )
        )
    )
VAR _pre =
    CALCULATE (
        MAX ( 'Incial Balance'[Incial Balance] ),
        FILTER (
            ALL ( 'Incial Balance' ),
            'Incial Balance'[Count No.] = MAX ( 'Incial Balance'[Count No.] )
                && 'Incial Balance'[Date]
                    = MAX ( 'Bank Statement'[Date] ) - 1
        )
    )
VAR cre_deb =
    CALCULATE (
        SUM ( 'Bank Statement'[Credits] ) - SUM ( 'Bank Statement'[Debits] ),
        FILTER (
            ALL ( 'Bank Statement' ),
            'Bank Statement'[Date]
                = MAX ( 'Bank Statement'[Date] ) - 1
        )
    )
RETURN
    IF ( _inci <> BLANK (), _inci, _pre ) + cre_deb

Or use LASTNOTBLANK() ,it's an easy and effective method:

Incial Balance2 =
VAR _last =
    LASTNONBLANK ( 'Incial Balance'[Incial Balance], [Incial Balance] )
VAR cre_deb =
    CALCULATE (
        SUM ( 'Bank Statement'[Credits] ) - SUM ( 'Bank Statement'[Debits] ),
        FILTER (
            ALL ( 'Bank Statement' ),
            'Bank Statement'[Date]
                = MAX ( 'Bank Statement'[Date] ) - 1
        )
    )
RETURN
    _last + cre_deb

Ending Balance =
[Incial Balance] + SUM ( 'Bank Statement'[Credits] )
    - SUM ( 'Bank Statement'[Debits] )

The final output is shown below:

Please take a look at the pbix file here.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Accumulated Balances

3 Replies

Related content

Rolling accumulation

Rolling accumulation

Accumulated average

Accumulated Balance

Inventory and Stock balance calculation