Help with openning and closed balance

[AndrejZitnay]

Hello all,

Would you be so kind and help me out with openning and closed balances?

I have 4 measures from 3 tables and 1 merge date table for months.

I struggle to find solution for openning and closed balance.

Many thanks.

Andrej

[amitchandak]

@AndrejZitnay , I am hoping you are using a common date table 

Sum of all the measures other then opening balance

All measure = [Measure from Table 1] + ....

Closing balance  = CALCULATE([All measure ],filter(allselected(Table),Table[Date] <=max(Table[Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[AndrejZitnay]

Hello @amitchandak ,

Can I have one follow up question?

I have my table for 3 years and at the end I'll end up with zero.

That's fine.

For another calculation I have to add together ClosingBalance & Monthly new.

(this is my base for series of important measures)

All my measures are fine on monthly basis but total for the year or overall 3 years doens't add up.

I think that comes with nature of Closing Balance Formula)

It is not possible to sum all motnhs together.

Is there some separate work around where I could sum my closing balances?
That will be just base of my follow up measurments.

thanks.

Andrej

[AndrejZitnay]

Hello @amitchandak 

You are star.

I have my closing balance now.

How I can get opening balance?

I know that it should be closing balance from last month.

Thanks.

Andrej

[amitchandak · Accepted solution]

@AndrejZitnay , One month less should do

Opening balance = CALCULATE([All measure ],filter(allselected(Table),Table[Date] <=maxX(Table, dateadd(Table[Date],-1, month))))

[mehmet]

Hi, I applied to my problem but i faced dependency problem. You use filter so i wrote my calculated closing balance (that depends on opeing balance with formulas) to a table as calculated column and try to find opening balance

[AndrejZitnay]

@amitchandak 

thank you very much.

Much appreaciated.