topic Re: Problem with Running Total in DAX Commands and Tips

Problem with Running Total

twilbour — Tue, 24 Aug 2021 21:22:28 GMT

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.

Re: Problem with Running Total

amitchandak — Wed, 25 Aug 2021 02:31:35 GMT

twilbour , baed on what I got, a Meausre like this will not change with date filter

Cumm = CALCULATE([LineTotal],,filter(all('Date'),'Date'[date] <=max('Date'[date])))

Re: Problem with Running Total

Alexrai — Wed, 25 Aug 2021 02:45:13 GMT

I modified this a little from a similar question, it might help you too:

Add Contributions =

VAR ClientName = MAX(Facts[Name])

VAR CurrentDate = MAX('Date'[Date])

VAR Selected = CALCULATETABLE('Date', ALLSELECTED())

VAR PriorStart = CALCULATE(MIN(Facts[Date]), Facts[Name] = ClientName, Facts[Date]< CurrentDate, REMOVEFILTERS('Date'[Date]))

VAR PriorEnd = CALCULATE(MAX(Facts[Date]), Facts[Name] = ClientName, Facts[Date]< CurrentDate, REMOVEFILTERS('Date'[Date]))

VAR PriorValue = CALCULATE(SUM(Facts[Contribution]), Facts[Name]=ClientName, INTERSECT(DATESBETWEEN(Facts[Date], PriorStart, PriorEnd), Selected), REMOVEFILTERS('Date'[Date]))

VAR CurrentValue = SUM(Facts[Contribution])

VAR NewValue = CurrentValue + PriorValue

RETURN IF(ISBLANK(PriorStart), CurrentValue, NewValue)

Re: Problem with Running Total

twilbour — Wed, 25 Aug 2021 13:22:16 GMT

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.