Calculating Daily Compounding Penalities

[bgierwi2]

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!

[bgierwi2]

@AshokKunwar 

That worked!

I would never have come up with that

Thank you!

[AshokKunwar]

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"!

[bgierwi2]

@AshokKunwar 

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

[AshokKunwar · Accepted solution]

@bgierwi2 

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!