Show cumulative balance sheet per month

[felixstuyck]

I want to show a the (cumulative) balance sheet for each month.

In my rows I have the different balance sheet categories and in my columns i have my months. (It is data from 1 year)

In short I have 3 tables:

-a date table

-a general ledger with bookings (CombinedFinTran)

- a 'grouping' table with all my catagories (GLaccounts)

Currently I can show the cumulative balance totals for every category(row's), but only if there is a booking in my general ledger in that month, if not it shows nothing but it is taken into account into the categories above. You can see what it should say in the picture.

This is the dax function i have for my value's: 

YTD_balans_Comb =

CALCULATE(SUM(CombinedFinTran[Value])*-1,

FILTER(ALLEXCEPT(CombinedFinTran,GLaccounts[Balans 1],GLaccounts[Balans 2],GLaccounts[Balans 3],GLaccounts[Omschrijving+],CombinedFinTran[Bkst.nr.],CombinedFinTran[Country])

,if(MAX(CombinedFinTran[Boekjaar / Periode])=BLANK(),CombinedFinTran[Boekjaar / Periode] <= PREVIOUSMONTH(CombinedFinTran[Boekjaar / Periode]), CombinedFinTran[Boekjaar / Periode] <= MAX(CombinedFinTran[Boekjaar / Periode]))))

In short the problem is that I refer to my columns with "MAX(CombinedFinTran[Boekjaar / Periode])" but if no booking is made in that period in that specific catagory the function returns blank.

Is there an other way to refer to the column or is there an other way to solve my problem?

[v-frfei-msft]

Hi @felixstuyck,

Kindly share your pbix to me.

Regards,

Frank

[felixstuyck]

Did you get the chanse to take a look at it?

[felixstuyck]

Hi,

I hope you can help me, here is the reduced data.

In 'Balance MTD' you find the monthly changes.

In 'Balance YTD' you see the cumulative balance sheet each month this is my goal, but here it is created with measures (not optimal solution).

In 'balance wish' you see the balance sheet with measure YTD_Combined. Here you find the formula is posted previuesly en here you see the problem. If no data is found for that category in that month it does not show the number, but is is included in the total sum.

Thanks in advance for taking a look.

It is my first project with power bi and i hope it will be a succes. 

[felixstuyck]

https://1drv.ms/f/s!AkjZXEM9ne6RgRFrU00jGKKbMdnl

Data didn't come true.