Problem with Running Total

[twilbour]

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.

[Alexrai]

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)

[amitchandak]

@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])))

[twilbour]

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.