Forum Discussion
Calculating Daily Compounding Penalities
Trying to get this to calculate in Power Bi Power Query or Dax.
I'm not sure which would be better, or is it possible to do it in both?
I have a data set that has a daily car count, with a target car count.
Every car that is above the target car count gets a penalty. The penalty is per car, per day.
Penalty Costs
| Occurance | Penalty |
| 1 | $ 500 |
| 2 | $ 500 |
| 3 | $ 1,000 |
| 4 | $ 1,000 |
| 5 | $ 1,000 |
| 6 | $ 1,500 |
| 7 | $ 1,500 |
| 8 | $ 1,500 |
So when I have a car above target I pay that penalty. On the first car that is over, I pay the first penalty cost ($500). But with multiple cars over I can pay multiple penalties per day. Once I pay the first penalty, the next penalty will move onto the second penalty and never back to the first. And so on
See below:
The details in "( )" are for reference to explain the math. I just need the cost.
Car Counts
| Date | Car Count | Cart Target | Car Penalty | Penalty Cost / Day |
| 1/1/2026 | 12 | 10 | 2 | $1,000 ($500 First Occurance + $500 Second Occurance) |
| 1/2/2026 | 11 | 10 | 1 | $1,000 ($1,000 Third Occurance) |
| 1/3/2026 | 10 | 10 | 0 | 0 |
| 1/4/2026 | 12 | 10 | 3 | $3,500 ($1,000 Fourth Occurance + $1,000 Fifth Occurance + $1,500 Sixth Occurance) |
| 1/5/2026 | 9 | 10 | 1 | $1,500 ($1,500 Seventh Occurance) |
I have these are two as independent data sets.
How would I be able to keep the running count of what occurance I am on so the next charges correctly, while allowing multiple penalties per day, while keeping the penalties the data set chronologically so they charge in sequential order?
This is a difficult one, I would appreciate any help!
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!
4 Replies
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!
