topic Re: Create a new column that calculates a running total for each unique ID in another column in DAX Commands and Tips

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

Anonymous — Wed, 04 Dec 2019 17:39:53 GMT

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.

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

Nathaniel_C — Wed, 04 Dec 2019 19:00:45 GMT

Hi Anonymous ,

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

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

Nathaniel_C — Wed, 04 Dec 2019 20:03:46 GMT

Hi Anonymous
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

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

Anonymous — Mon, 09 Dec 2019 16:42:59 GMT

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!

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

Nathaniel_C — Mon, 09 Dec 2019 17:09:18 GMT

Anonymous ,
You are welcome!

Nathaniel