Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Calculating Balances based on values from another Table

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

Need Help

2 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    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.

  • sraj's avatar
    sraj
    Responsive Resident
    6 years ago

    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.