Calculating Balances based on values from another Table

[Anonymous]

Hi there

I have 2 data tables (OpenStudentBal & Leavers) setup below in PowerBI.

OpenStudentBal table has 2 data columns: Mth/Yr & Opening Balance

Leavers table has 3 data columns: Mth/Yr, Batch and Leavers

Is there a way I can:

1. Take the opening balance for the month from the OpenStudentBal Table
2. Subtract Leavers sequentially (from Batch 1 to 26) to obtain a calculated column (Closing Balance) as of each Batch
3. Use the Closing Balance from previous Batch in the Opening Balance column
4. Repeat step 1 - 3 till the last Batch for the month

Thanks!

OpenStudentBal Table

Leavers Table

[sraj]

Hi Everyone,

Need help with something similar...would like to CALCULATE the Remaining amount.

Type     JOHN            DOE           REMAINING

A          500000           50000         450000  

B          100000           40000           60000

C             5000             2000              3000

Please help.

Thanks,

Seema.

[PattemManohar]

@Anonymous Please try the below steps:

Note - LkpSource is your lookup table and Data table is your source data table.

Create a new column as

ClosingBalance = 
VAR _OpeningBal = LOOKUPVALUE(Test197LkpSource[OpeningBalance],Test197LkpSource[MonthYear],Test197Data[MonthYear]) 
VAR _ClosingBalance = CALCULATE(SUM(Test197Data[Leavers]),FILTER(ALL(Test197Data),Test197Data[Batch]<=EARLIER(Test197Data[Batch]) && Test197Data[MonthYear] = EARLIER(Test197Data[MonthYear])))
RETURN _OpeningBal - _ClosingBalance

Then create a Opening Balance as

OpeningBalance = 
VAR _MainOpeningBal = LOOKUPVALUE(Test197LkpSource[OpeningBalance],Test197LkpSource[MonthYear],Test197Data[MonthYear])
VAR _OpeningBal = LOOKUPVALUE(Test197Data[ClosingBalance],Test197Data[MonthYear],Test197Data[MonthYear],Test197Data[Batch],Test197Data[Batch]-1) 
RETURN IF(Test197Data[Batch]=1,_MainOpeningBal,_OpeningBal)

Here is the final output

Also, Please post your sample data in copiable format which will save a lot of time.