Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I am creating a general ledger detail report, that shows all the transactions within a department, account and date range. I want a running total measure that starts with the first displayed transaction (for that department/account/date) and calculates the total through to the last transaction. I was able to get a full year running balance, but as soon as I apply a date filter it doesn't work. Also doesn't work if there are two transactions on the same date.
I added an index column to aid - and again it works for the whole year (Ignores the date), or it gives me just the individual transaction total as the "balance".
I have a calendar table and a GL table joined by date.
My GL table is like this:
Index Dept Account Date Type Num Name Memo Debt Credit
1 A Sales 1/1/21 JE 123 Smith blah 100 0
2 A Sales 1/1/21 Bill xyz Jones blah 0 25
3 A Sales 2/1/21 Invoice xyz Jones blah 0 200
4 A Cost 1/15/21 JE 123 Smith blah 100 0
5 B Sales 1/12/21 Bill xyz Jones blah 0 50
6 B Sales 1/12/21 Invoice xyz Jones blah 0 200
What I want to see is this - for January
Dept Account Date Type Num Name Memo Debit Credit Balance
A Sales 1/1/21 JE 123 Smith blah 100 0 100
A Sales 1/1/21 Bill xyz Jones blah 0 25 75
Dept Account Date Type Num Name Memo Debit Credit Balance
A Cost 1/15/21 JE 123 Smith blah 100 0 100
Dept Account Date Type Num Name Memo Debit Credit Balance
B Sales 1/12/21 Bill xyz Jones blah 0 25 25
B Sales 1/12/21 Invoice xyz Jones blah 0 200 200
The same would be true for every month - I don't want a carryforward balance.
February would look like this:
Dept Account Date Type Num Name Memo Debit Credit Balance
A Sales 2/1/21 JE 123 Smith blah 0 200 200
I have a calendar table and a GL table joined by date.
The formula I have is this:
VAR MinIndex = MIN(GLData[Index])
VAR MaxIndex = Max(GLData[Index])
RETURN CALCULATE([LineTotal],FILTER(GLData,MinIndex<=MaxIndex))
This just returns the line amount to the balance field...it doesn't accumulate.
I modified this a little from a similar question, it might help you too:
This doesn't work. Tried this as one of my earlier iterations. This results in every date (whether blank or not) being added to the display. So I get at least 30 rows per section - most blank.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
11 |