- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Help with Running Totals
Hi All
I have a table called month_vals which just has 12 months (field is called Month Short) and an FY Order column which numbers the months starting with Oct as 1, Nov as 2, etc.. It serves as the x-axis on my line combo chart. It is joined to another table called Commitment_Docs. The Commitment_Docs table has the transactions I need to turn into a cumlative running total; this table also has a date field. Power Bi generated the below formula for the running balance. It works except it takes the amount for the current month and puts it in the future months. I need it to stop at the current month. Does anyone have a solution on how to modify?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
hi, @Userpath77
try below
CALCULATE(
SUM('Commitment_Docs'[Commitment Amt]),
FILTER(
allselected('Month_Vals',
'Month_Vals'[FY Order],
'Month_Vals'[Month Short]
),
'Month_Vals'[FY Order] <= MAX('Month_Vals'[FY Order])
&& 'Month_Vals'[Month Short] < MAX('Month_Vals'[Month Short])
)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Dangar. When I enter yoru formula, I get a message that multiple arguments are not allowed in the ALLSELECTED function when the first argument is a table reference.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi, @Userpath77
In above code update allselected
CALCULATE(
SUM('Commitment_Docs'[Commitment Amt]),
FILTER(
allselected(
'Month_Vals'[FY Order],
'Month_Vals'[Month Short]
),
'Month_Vals'[FY Order] <= MAX('Month_Vals'[FY Order])
&& 'Month_Vals'[Month Short] < MAX('Month_Vals'[Month Short])
)
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
It looks like you are trying to create a cumulative running total for the 'Commitment Amt' column in the 'Commitment_Docs' table, based on the 'Month Short' and 'FY Order' columns from the 'Month_Vals' table. The issue you're facing is that the current formula is including future months in the cumulative total.
To stop the cumulative total at the current month, you can modify the formula by changing the condition in the FILTER function. Specifically, you want to include only the rows where the 'Month Short' and 'FY Order' values are less than or equal to the maximum values for the same columns. Here's a modified version of your formula:
CALCULATE(
SUM('Commitment_Docs'[Commitment Amt]),
FILTER(
CALCULATETABLE(
SUMMARIZE('Month_Vals', 'Month_Vals'[FY Order], 'Month_Vals'[Month Short]), ALLSELECTED('Month_Vals')
), 'Month_Vals'[FY Order] <= MAX('Month_Vals'[FY Order])
&& 'Month_Vals'[Month Short] <= MAX('Month_Vals'[Month Short]) ) )
In this modified version, I replaced the ISONORAFTER function with a condition that checks if the 'FY Order' and 'Month Short' values are less than or equal to the maximum values for the same columns. This change ensures that only the rows up to and including the current month are considered in the cumulative total.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thanks 123abc. This is getting close. When I plugged in your changes it was showing the months of Oct, Nov and the last month of Sep. It should only show Oct and Nov. I changed your formula below just removing the <= MAX to = MAX. That change fixed my months where I just see values on Oct and Nov. The problem now is that the values are not showing cumulative, they show the incremental values for Oct and Nov. Is there a solution for this? (My minor changes removing <= show below)

Helpful resources
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.

Subject | Author | Posted | |
---|---|---|---|
05-20-2025 06:00 AM | |||
06-10-2025 07:19 AM | |||
05-19-2025 12:44 AM | |||
05-15-2025 05:11 AM | |||
05-18-2025 05:38 PM |
User | Count |
---|---|
9 | |
9 | |
6 | |
6 | |
6 |
User | Count |
---|---|
15 | |
13 | |
11 | |
9 | |
7 |