cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Patron

## Help 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

1 ACCEPTED SOLUTION
Super User

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

Post Patron

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

Post Patron

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

Super User

@AndrejZitnay , One month less should do

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

Frequent Visitor

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

Post Patron

thank you very much.

Much appreaciated.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors