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!
Hii bgierwi2
First, you need to know exactly which "Occurrence numbers" apply to each day. Add these Calculated Columns to your Car Counts table:
-- 1. Number of cars over target for this specific day
CarsOverTarget = MAX(0, 'Car Counts'[Car Count] - 'Car Counts'[Target])
-- 2. Total cars over target up to (and including) this day
RunningTotalOver =
CALCULATE(
SUM('Car Counts'[CarsOverTarget]),
FILTER(
'Car Counts',
'Car Counts'[Date] <= EARLIER('Car Counts'[Date])
)
)
Create the Sequential Penalty Measure
Now, we calculate the cost. We identify the range of occurrences for the day (e.g., if we were at 3 total and today we have 2 more, we need the costs for occurrences 4 and 5).
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),
-- This logic mirrors your escalating penalty table
VAR _ID = [Value]
RETURN
IF(_ID <= 2, 500, 500 + (INT(DIVIDE(_ID - 3, 3)) + 1) * 500)
),
0
)
Summary for the Community
To solve escalating penalties that span multiple days, use a Running Total column to track the "Occurrence ID." Then, use SUMX over a GENERATESERIES range to calculate the specific cost for that day's window of occurrences.
If this sequential logic correctly calculates your compounding car penalties, please mark this as the "Accepted Solution"!
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!
