Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I need help!
I have the GL transactions of all main accounts, below is the sample data :
I want to write measures for calculating:
Opening balance: This will be sum of all transaction before the start date of the date range filter for the specific main account
Debit : This will be sum of all values under 'debit' for the same main account with in the the date range
Credit: This will be sum of all values under 'credit' for the same main account with in the date range
Closing balance: This will be calculated as sum of opening balance, debit and credit for the specific main account
The resulting table should look like:
Solved! Go to Solution.
Try this with Date Calendar and join it Accounting date
The opening is sum of debit and credit till last month
The closing is sum of debit and credit till this month
All are new measures
Debit = SUM(Table[Debit])
Credit =SUM(Table[Credit])
Opening balance = CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))
+ CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))
closing balance = CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,date[date])))
+ CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,date[date])))
Try this with Date Calendar and join it Accounting date
The opening is sum of debit and credit till last month
The closing is sum of debit and credit till this month
All are new measures
Debit = SUM(Table[Debit])
Credit =SUM(Table[Credit])
Opening balance = CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))
+ CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,max(dateadd(date[date]),-1,Month))))
closing balance = CALCULATE(SUM(Table[Debit]),filter(date,date[date] <=maxx(date,date[date])))
+ CALCULATE(SUM(Table[Credit]),filter(date,date[date] <=maxx(date,date[date])))
Hi Thanks for helping! I am able to get the measured values for debit and credit but can you please tell me how to "Try this with Date Calendar and join it Accounting date" as I am not getting the opening and closing balance values
Thanks!
Sebin
Probably too late, but did you solve the calculation? I have the same problem, please help !!!
I did calculate the timeframes to be calculated.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |