cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Warthog710
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

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

runntot1.PNG

 


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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
Nathaniel_C
Super User I
Super User I

Hi @Warthog710 ,
runntot.PNG

 

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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

runntot1.PNG

 


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 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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!

@Warthog710 ,
You are welcome!

Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors