Forum Discussion
Need help with opening balance
Hi,
I am having issues getting a measure right, I tried to look on the forums and google but I can't seem to find something that works.
So this is my situation:
I have a table with opening balances for different catagories
Let's use the below as an example:
| Catagory | Year | Opening Balance |
| 1 | 2018 | 1000 |
| 2 | 2020 | 2500 |
| 3 | 2020 | 3000 |
I have a different table with the catagory:
| Catagory | Catagory Code | Catagory Description |
| 1 | 0040 | Personal Bank Account |
| 2 | 0283 | Customer Invoices |
Transaction (fact) table:
| Entry number | Amount | Catagory Code | Date |
| 231 | 300 | 5 | 01-02-2019 |
| 3453 | 500,50 | 2 | 05-06-2017 |
| 343 | 200 | 1 | 20-10-2018 |
And I have a date table.
Fact table is connected to catagory and date table
Opening balance table is connected to the catagory table
What I need at the end is a barchart and a table visual to see how everything changes per month, taking the opening balance in account for each year.
I can get a table with all the values without measure no problem, relationships seem to work fine.
Thank you in advance for your help!
My previous topic got marked as spam? Not sure why it was deleted..
Anonymous , In opening balances you can create a date like
Date = Date([Year],1,1)
Join it with date and Catagory too
You can use YTD from both opening balances and Transaction and add it.
YTD = CALCULATE(sum(Opening Balance[Opening Balance])+ sum(Transaction[Amount]),DATESYTD('Date'[Date],"12/31"))
Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
3 Replies
- amitchandak
Super User
Anonymous , In opening balances you can create a date like
Date = Date([Year],1,1)
Join it with date and Catagory too
You can use YTD from both opening balances and Transaction and add it.
YTD = CALCULATE(sum(Opening Balance[Opening Balance])+ sum(Transaction[Amount]),DATESYTD('Date'[Date],"12/31"))
Power BI — YTD Questions — Time Intelligence 1–5
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38aI think that did the trick, thank you! amitchandak
amitchandak can you maybe help me with one more thing regarding this?
I tried to put a filter in the measure, but then everything bundles into january, if I have no filter it shows in all months. Do you know how to fix that?
CALCULATE(SUM(BalanceLines[OPEN])+SUM(TransactionLinesBulk[Bedrag]),DATESYTD(Dates[Date],"31-12"), FILTER(GLAccountClass, LEFT(GLAccountClass[ClassificationCode],4)="BIva" || LEFT(GLAccountClass[ClassificationCode],4)="BMva" || LEFT(GLAccountClass[ClassificationCode],4)="BEff" || LEFT(GLAccountClass[ClassificationCode],4)="BLim" || LEFT(GLAccountClass[ClassificationCode],4)="BFva" || LEFT(GLAccountClass[ClassificationCode],4)="BVor" || LEFT(GLAccountClass[ClassificationCode],4)="BVrd" || LEFT(GLAccountClass[ClassificationCode],4)="BPro" ))Measure 3 without filter
Measure 2 with filter (also marked yellow underneath)