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, B...
Need Help
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.