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

[Anonymous]

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.

[Nathaniel_C]

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 

[Nathaniel_C · Accepted solution]

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 

[Anonymous]

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!

[Nathaniel_C]

@Anonymous ,
You are welcome!

Nathaniel