Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
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).
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:
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:
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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |