Forum Discussion
Calculating Daily Compounding Penalities
Your current running total is "Global," meaning it counts every car regardless of the group. To make groups accumulate penalties independently, the DAX needs to "partition" the calculation by the Group name.
The Solution: Group-Aware DAX logic
Step 1: Update the Running Total (The "Partition" Logic)
Replace your previous RunningTotalOver column with this version. It adds a second filter condition to only sum values where the Group matches the current row.
RunningTotalOver = CALCULATE( SUM('Car Counts'[CarsOverTarget]), FILTER( 'Car Counts', 'Car Counts'[Group] = EARLIER('Car Counts'[Group]) && 'Car Counts'[Date] <= EARLIER('Car Counts'[Date]) ) )Step 2: The Penalty Measure (No changes needed, but for reference)
Your penalty measure will now automatically work correctly because it relies on the RunningTotalOver. Since that total now resets for each group, the _StartOccurrence and _EndOccurrence variables will start back at 1 for "Red," "Green," or any future groups
Penalty Cost / Day = VAR _CurrentTotal = 'Car Counts'[RunningTotalOver] VAR _DailyOver = 'Car Counts'[CarsOverTarget] VAR _StartOccurrence = _CurrentTotal - _DailyOver + 1 VAR _EndOccurrence = _CurrentTotal RETURN IF( _DailyOver > 0, SUMX( GENERATESERIES(_StartOccurrence, _EndOccurrence), VAR _ID = [Value] RETURN IF(_ID <= 2, 500, 500 + (INT(DIVIDE(_ID - 3, 3)) + 1) * 500) ), 0 )Why this fixes the final detail:
- Independence: The Group = EARLIER(Group) part of the formula acts like a "Partition By" clause in SQL. It creates a "silo" for each color.
- Scalability: If you add "Group Yellow" or "Group Green" tomorrow, the formula will automatically start a new 1-to-1000 sequence for them without any code changes.
- Accuracy: It handles the transition perfectly. Even if Group Blue and Group Red have data on the same date (though you mentioned they don't currently), this logic would still keep their penalty buckets separate.
Since this completes your requirements for independent group tracking, please mark this as the "Accepted Solution"! This should be your final badge for this thread!
I have one final detail I need to add to complete what I need
There is also a Group column.
Below is Group Blue and Red. And as the data set grows, additional Groups may be added.
Each of those groups accumulate penalities independently.
Is there a way so each group accumulates penalites independently?
Same rules of how penalties accumulate. Where sequentially the penalities increase.
There will not be entries that have multiple groups on the same day.
| Date | Car Count | Cart Target | Car Penalty | Group | Penalty Cost |
| 1/1/2026 | 12 | 10 | 2 | Blue | $1,000 ($500 First Occurance + $500 Second Occurance) |
| 1/2/2026 | 11 | 10 | 1 | Blue | $1,000 ($1,000 Third Occurance) |
| 1/3/2026 | 10 | 10 | 0 | Blue | 0 |
| 1/4/2026 | 12 | 10 | 3 | Blue | $3,500 ($1,000 Fourth Occurance + $1,000 Fifth Occurance + $1,500 Sixth Occurance) |
| 1/5/2026 | 9 | 10 | 1 | Blue | $1,500 ($1,500 Sevent Occurance) |
| 1/6/2026 | 10 | 10 | 0 | Red | 0 |
| 1/7/2026 | 11 | 10 | 1 | Red | $500 ($500 First Occurance) |
| 1/8/2026 | 12 | 10 | 2 | Red | $1500 ($500 Second Occurance + $1000 Third Occurance) |
| 1/9/2026 | 12 | 10 | 2 | Red | $2000 ($1000 Fourth Occurance + $1000 Fifth Occurance) |
| 1/10/2026 | 11 | 10 | 1 | Red | $1500 ($1500 Sixth Occurance) |
| 1/11/2026 | 8 | 10 | 0 | Red | 0 |
This would complete everything I was hoping to get out out of this calculation
Your current running total is "Global," meaning it counts every car regardless of the group. To make groups accumulate penalties independently, the DAX needs to "partition" the calculation by the Group name.
The Solution: Group-Aware DAX logic
Step 1: Update the Running Total (The "Partition" Logic)
Replace your previous RunningTotalOver column with this version. It adds a second filter condition to only sum values where the Group matches the current row.
RunningTotalOver =
CALCULATE(
SUM('Car Counts'[CarsOverTarget]),
FILTER(
'Car Counts',
'Car Counts'[Group] = EARLIER('Car Counts'[Group]) &&
'Car Counts'[Date] <= EARLIER('Car Counts'[Date])
)
)
Step 2: The Penalty Measure (No changes needed, but for reference)
Your penalty measure will now automatically work correctly because it relies on the RunningTotalOver. Since that total now resets for each group, the _StartOccurrence and _EndOccurrence variables will start back at 1 for "Red," "Green," or any future groups
Penalty Cost / Day =
VAR _CurrentTotal = 'Car Counts'[RunningTotalOver]
VAR _DailyOver = 'Car Counts'[CarsOverTarget]
VAR _StartOccurrence = _CurrentTotal - _DailyOver + 1
VAR _EndOccurrence = _CurrentTotal
RETURN
IF(
_DailyOver > 0,
SUMX(
GENERATESERIES(_StartOccurrence, _EndOccurrence),
VAR _ID = [Value]
RETURN
IF(_ID <= 2, 500, 500 + (INT(DIVIDE(_ID - 3, 3)) + 1) * 500)
),
0
)
Why this fixes the final detail:
- Independence: The Group = EARLIER(Group) part of the formula acts like a "Partition By" clause in SQL. It creates a "silo" for each color.
- Scalability: If you add "Group Yellow" or "Group Green" tomorrow, the formula will automatically start a new 1-to-1000 sequence for them without any code changes.
- Accuracy: It handles the transition perfectly. Even if Group Blue and Group Red have data on the same date (though you mentioned they don't currently), this logic would still keep their penalty buckets separate.
Since this completes your requirements for independent group tracking, please mark this as the "Accepted Solution"! This should be your final badge for this thread!