Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
eah
Frequent Visitor

How to get correct Incoming Balance / Balance Sheet

I have some troubles getting my balance sheet correct.

 

My transaction-table looks like this: 

 

AccountNumberPeriod/MonthYearIncoming balanceChangeClosing balance
10001

2017

0500500
1000220175007001200
1000320171200-2001000
1000420171000-500500
220032018-1000-100-1100
220052018-1100-300

-1400

 

My table consists of 5 rows - AccountNumber, Period/month, Year, Incoming balanse, Change and Closing balance. Every time there is a transaction on a specific account there is a new row in the table retrieving the most recent closing balance for that account-number as Incoming balance (if no previous transactions on the specific account, the value for Incoming balance naturally is 0). An example of transactions  in 2016 would look like this (assuming last closing balance equals 2000): 

 

AccountNumberPeriod/MonthYearIncomingBalanceChangeClosingBalance
14002201620003002300
140052016230010003300

 

But let's say I want to analyze the year 2016. Before the respective transactions above, there has not been any registered transactions in 2016. The last registered transaction was in 2014 and gave a ClosingBalance of 2000. In other words I want a DAX-expression returning the last registered ClosingBalance from previous years as IncomingBalance for 2016. Example:

 

Year2016
Account NumberIncoming BalanceChangeClosingBalance
1400200013003300

 

 

 



3 REPLIES 3
lbendlin
Super User
Super User

You need to compute this for the month, not for the year.  (That way it will automatically work when you hide the month)

 

It always helps to break the problem down into baby steps

 

- make a table with the account, month and year

- create a measure

- add a variable for the selected account, and a variable for the selected yearmonth (combination of month and year, for example by multiplying the year by 100 and adding the month)

- calculate the maximum yearmonth for the account that is smaller than your selected yearmonth

- calculate the closing balance for that account and calculated yearmonth.

 

That will give you the opening balance for the selected "row" (which in your case happens to be "2016", but in reality will be the first month of 2016 that you have data for).

eah
Frequent Visitor

Thank you for your answer @lbendlin

 

I have tried the approach you listed, but I have not quite managed to find the correct solution. 

 

1. My table looks like this: 

eksempel balanse1.png

 

As you can see I now have, as you suggested, a column in the format YYYYMM, this is the one I have linked to my datetable. There is also a column for account in every row in the table above.  IB is incoming balance and CB is closing balance. 

 

2. I have tried to make the measure you outlined above, but I have not managed to make it work properly.

 

3.  I have created this measure to calculate the maximum YYYYMM for the account that is smaller than my selected filter: 

 

MAXYearMonth =
var YearMonth = MAXX(ALLSELECTED('Datotabell - Master'), 'Datotabell - Master'[Date])
var Account = SELECTEDVALUE(Ac[AcNo])
Return

CALCULATE(MAX(AcBal[dateFormat]), FILTER(ALL('Datotabell - Master'),'Datotabell - Master'[Date] < YearMonth))
 
This gives me the last date there was a transaction on the specific account: 
 
lastTransaction.png
 
As you can see there has not been any transactions on account "1937" since 2019-12, but the CB registered in my transaction table for this account is what I want to be shown as IB if I i.e. choose 2021 as year.

 

4. After this I tried making a measure that gives me the last calculated closing balance for the account where YYYYMM is smaller than selected date.

 

IB =
CALCULATE(SUM(AcBal[CB]), FILTER(AcBal,[MAXYearMonth])).
 
This is where I think I am doing something wrong and I am not sure how to solve it.
 
eah
Frequent Visitor

Hello @lbendlin! Have you had the chance to check this out? 🙂 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors