Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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
Proud to be a Super User!
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!
@Anonymous ,
You are welcome!
Nathaniel
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |