cancel
Showing results for
Did you mean:
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:

 AccountNumber Period/Month Year Incoming balance Change Closing balance 1000 1 2017 0 500 500 1000 2 2017 500 700 1200 1000 3 2017 1200 -200 1000 1000 4 2017 1000 -500 500 2200 3 2018 -1000 -100 -1100 2200 5 2018 -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):

 AccountNumber Period/Month Year IncomingBalance Change ClosingBalance 1400 2 2016 2000 300 2300 1400 5 2016 2300 1000 3300

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:

 Year 2016 Account Number Incoming Balance Change ClosingBalance 1400 2000 1300 3300

3 REPLIES 3
Super User III

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).

Frequent Visitor

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

1. My table looks like this:

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:

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.

Frequent Visitor

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

Announcements