cancel
Showing results for
Did you mean:
Frequent Visitor

## Create a new column that calculates a running total for each unique ID in another column

I am having an issue creating a new column that calculates a running total for each unique ID listed. For example:

ID          Bill          Date          Running Total (this is what I need)

1           105         1/1/19       105

2           103         1/1/19       103

1           110         2/1/19       215

2           98           2/1/19       201

3           132         2/1/19       132

Now, I have sucessfully created a measure that calculates the running total, but I am having difficulty figuring out the right filter, or group.... to use. Here is what I have so far.

``````Total = CALCULATE(
SUM('Table1'[Bill]),
FILTER('Table1', 'Table1'[Date] <= EARLIER('Table1'[Date]))
)``````

This calculation works and creates a valid result. However, it is the sum for all the Id's listed.

My main issue, is my Id field is not distinct, because each Id has a monthly entry, it appears numerous times. My goal is to create a column that shows the sum of the current bill and all previous bills for each Id.

Any assistance would be much appreciated.

1 ACCEPTED SOLUTION
Super User I

Hi @Warthog710
Or this:

``````Running Total 2 =
Var _curDate = myTable[Date]
Var _curID = myTable[ID]
var _calc = CALCULATE(SUM(myTable[Bill]),myTable[Date]<=_curDate,myTable[ID]=_curID, All(mytable))
return _calc``````

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

4 REPLIES 4
Super User I

Hi @Warthog710 ,

Try this:

``````Running Total =
VAR _curDate = myTable[Date]
VAR _calc =
CALCULATE (
SUM ( myTable[Bill] ),
FILTER ( ALLEXCEPT ( myTable, myTable[ID] ), MAX ( myTable[Date] ) <= _curDate )
)
RETURN
IF ( ISBLANK ( _calc ), myTable[Bill], _calc )``````

Using variables in your DAX rather than Earlier is recommended now.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Super User I

Hi @Warthog710
Or this:

``````Running Total 2 =
Var _curDate = myTable[Date]
Var _curID = myTable[ID]
var _calc = CALCULATE(SUM(myTable[Bill]),myTable[Date]<=_curDate,myTable[ID]=_curID, All(mytable))
return _calc``````

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Frequent Visitor

Thanks for the assistance! I ended up using the second solution you posted. It is correctly calculating the running total bill for each account ID in my data as I intended. Awesome job!

Super User I

@Warthog710 ,
You are welcome!

Nathaniel

Proud to be a Super User!

Announcements