Forum Discussion
Post PatronHelp with openning and closed balance
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
AndrejZitnay , One month less should do
Opening balance = CALCULATE([All measure ],filter(allselected(Table),Table[Date] <=maxX(Table, dateadd(Table[Date],-1, month))))
6 Replies
- amitchandak
Super User
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
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
AndrejZitnay , One month less should do
Opening balance = CALCULATE([All measure ],filter(allselected(Table),Table[Date] <=maxX(Table, dateadd(Table[Date],-1, month))))
- 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
