Need help with opening balance

[Anonymous]

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

[amitchandak · Accepted solution]

@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

[Anonymous]

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

[Anonymous]

I think that did the trick, thank you! @amitchandak