Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
@AndrejZitnay , One month less should do
Opening balance = CALCULATE([All measure ],filter(allselected(Table),Table[Date] <=maxX(Table, dateadd(Table[Date],-1, month))))
@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.
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
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
@AndrejZitnay , One month less should do
Opening balance = CALCULATE([All measure ],filter(allselected(Table),Table[Date] <=maxX(Table, dateadd(Table[Date],-1, month))))
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
108 | |
107 | |
89 | |
61 |
User | Count |
---|---|
171 | |
139 | |
133 | |
103 | |
86 |