Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
twilbour
Helper I
Helper I

Problem with Running Total

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.

 

 

3 REPLIES 3
Alexrai
Helper I
Helper I

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)

 

Alexrai_1-1629859449348.png

 

amitchandak
Super User
Super User

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors