Introduction
, one of my clients asked me the following question:
My company applies a limit on the overtime balance each employee can accumulate. This Limit is at 20 hours in total.
If an employee surpasses this limit, overtime exceeding 20 hours will be reduced.
How can I calculate this in Power BI?
This sounds like an easy job.
Add a measure that checks if the total amount of overtime exceeds 20, and if so, return 20. Isn’t that correct?
Let’s try a DAX measure
First, we must create a measure that adds up all overtime done since the beginning of time to calculate the total balance:
Overtime Overall =
VAR FirstOT = CALCULATE(FIRSTNONBLANK('Date'[Date]
,[Overtime])
,REMOVEFILTERS('Date')
)
VAR MaxDate = MAX('Date'[Date])
RETURN
CALCULATE([Overtime]
,REMOVEFILTERS('Date')
,DATESBETWEEN('Date'[Date], FirstOT, MaxDate)
)Second, we create a measure to set the upper limit to 20:
Overtime corrected (try 1) =
VAR OvertimeOverall = [Overtime Overall]
RETURN
IF(OvertimeOverall > 20, 20, OvertimeOverall)Let’s see the result:
It looks correct.
Upon closer examination of the results, we find that they don’t add up correctly.
Look at Mandy Jones (Name is invented):
Starting from April, she worked fewer hours to reduce her overtime balance.
But the measure [Overtime Overall] still shows more than 20 hours in April, even though the amount was corrected in March.
This isn’t correct.
The measure [Overtime Overall] must consider the corrections.
Moreover, the total is wrong. Since the total is always above 20, it will also display 20.
Target of the calculation
Before we proceed, we must define the requirements to determine what needs to be done.
First, the results must be shown only at the monthly level.
Second, as mentioned above, the results must reflect the corrections made in the preceding month(s).
Third, we must be able to see the corrections made for each month.
Try a DAX approach
OK, what happens when we precalculate a table for each Employee in the measure and correct the values according to it:
Overtime Corrected (try 2) =
VAR ActEmpId = SELECTEDVALUE('Overtime'[EmployeeID])
VAR EmpsByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Overtime'[EmployeeID]
,'Date'[Last Day Of Month]
,"OvertimeCorrected", IF([Overtime Overall] > 20.0, 20.0, [Overtime Overall])
)
,'Overtime'[EmployeeID] = ActEmpId
)
RETURN
SUMX(EmpsByMonth, [OvertimeCorrected])The filter added with CALCULATETABLE() is necessary to reduce the size of the table generated with SUMMARIZECOLUMNS().
As you can see, the results are identical, as the measure still doesn’t consider the corrections in the previous months.
Interestingly, the total is blank.
The reason is that SELECTEDVALUE() doesn’t return anything, and thus, there is nothing to calculate.
I can solve this by using VALUES():
Overtime Corrected (try 2) =
VAR ActEmpId = VALUES('Overtime'[EmployeeID])
VAR EmpsByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Overtime'[EmployeeID]
,'Date'[Last Day Of Month]
,"OvertimeCorrected", IF([Overtime Overall] > 20.0, 20.0, [Overtime Overall])
)
,'Overtime'[EmployeeID] IN ActEmpId
)
RETURN
SUMX(EmpsByMonth, [OvertimeCorrected])Here is the result with the correct total:
But this approach doesn’t scale well, as the measure must generate the table for all employees during the calculation.
It’s OK with 63 employees, as my data has, but it’s a different story with hundreds or thousands of employees.
However, the core issue remains: How can we calculate the correct result for each month and each employee while maintaining the correct total?
Finding the solution
The approach to calculate the correct results should be to check the overtime for each month, taking into account any previous corrections.
For this, the value of the previous month must be checked to see if it was corrected.
The overtime balance can then be updated with the amount of overtime for the current month.
This would involve a recursive calculation, where the calculation for each row uses the result of the same column from the previous month.
Unfortunately, DAX doesn’t allow us to do it, as it will consider it a circular dependency:
When going back one step, the approach could be to develop it in Power Query.
I’m unsure if this would work, as it would require a procedural approach to process one row at a time.
I know that in SQL, this could be done relatively easily.
As the data source is an Azure SQL database, I decided to process it within that database.
Calculate the corrections in SQL
Taking into account the requirement that this data needs to be calculated on a monthly level, I created a new table to store the data with the corrections:
Here is the entire SQL code to calculate the correction for each employee and each month:
INSERT INTO [Evergreen].[Overtime_Correction]
([EmployeeID]
,[LastDayOfMonth]
,[SumOvertime])
SELECT [O].[EmployeeID]
,[D].[LastDayOfMonth]
,SUM([O].[Overtime]) AS [SumOvertime]
FROM [Evergreen].[Overtime] AS [O]
INNER JOIN [dbo].[Date] AS [D]
ON [D].[DateKey] = [O].[Datekey]
GROUP BY [O].[EmployeeID]
,[D].[LastDayOfMonth];
SET NOCOUNT ON;
DECLARE @EmployeeID int;
DECLARE @LastDayOfMonth date;
DECLARE @SumOvertime decimal(38, 3);
DECLARE @Overtime_Overall decimal(38, 3);
DECLARE @Overtime_Correction decimal(38, 3);
DECLARE @Overtime_Corrected decimal(38, 3);
DECLARE @SumCheck decimal(38, 3);
DECLARE @Overtime_Corrected_PM decimal(38, 3);
DECLARE @Set_Correction decimal(38, 3);
DECLARE @Set_Corrected decimal(38, 3);
UPDATE [Evergreen].[Overtime_Correction]
SET [Overtime_Correction] = NULL
,[Overtime_Corrected] = NULL;
DECLARE corr CURSOR FOR
SELECT [EmployeeID]
,[LastDayOfMonth]
,[SumOvertime]
,SUM([SumOvertime]) OVER (PARTITION BY [EmployeeID] ORDER BY [LastDayOfMonth] ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS [Overtime_Overall]
,[Overtime_Correction]
,[Overtime_Corrected]
FROM [Evergreen].[Overtime_Correction]
ORDER BY [LastDayOfMonth];
OPEN corr
FETCH NEXT FROM corr INTO @EmployeeID, @LastDayOfMonth, @SumOvertime, @Overtime_Overall, @Overtime_Correction, @Overtime_Corrected;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Overtime_Corrected_PM = ISNULL([Overtime_Corrected], 0)
FROM [Evergreen].[Overtime_Correction]
WHERE [EmployeeID] = @EmployeeID
AND [LastDayOfMonth] = EOMONTH(@LastDayOfMonth, -1);
SET @SumCheck = IIF(@Overtime_Corrected_PM IS NULL, @SumOvertime, @Overtime_Corrected_PM)
IF @Overtime_Overall IS NULL
BEGIN
SET @Set_Correction = 0;
SET @Set_Corrected = @SumOvertime;
END
ELSE
IF @SumCheck + @SumOvertime > 20
BEGIN
SET @Set_Correction = (@SumCheck + @SumOvertime) - 20;
SET @Set_Corrected = 20.0;
END
ELSE
BEGIN
SET @Set_Correction = 0.0;
SET @Set_Corrected = @SumCheck + @SumOvertime;
END
UPDATE [Evergreen].[Overtime_Correction]
SET [Overtime_Correction] = @Set_Correction
,[Overtime_Corrected] = @Set_Corrected
WHERE [EmployeeID] = @EmployeeID
AND [LastDayOfMonth] = @LastDayOfMonth;
FETCH NEXT FROM corr INTO @EmployeeID, @LastDayOfMonth, @SumOvertime, @Overtime_Overall, @Overtime_Correction, @Overtime_Corrected;
END
CLOSE corr;
DEALLOCATE corr;Line 39, where I calculate the running total, is interesting, as I use a not-so-well-known technique using the OVER() clause.
Regardless of whether you use T-SQL or not, you can use this process to calculate the needed results with any other programming language.
I’m very confident that I would have been able to write a self-recursive method to calculate the needed results without the use of a cursor.
However, I believe this approach is more straightforward to adapt to other languages.
When looking at Mandy Jones (EmpID 1253), the results are the following for 2025:
When checking them, you will notice that the corrections are applied correctly and the Overtime balance is correct.
Integrating the table in Power BI
The last step is to integrate the new table into Power BI.
I can simply import the table into the data model and add Relationships to the other tables:
Now, I can create the measures to calculate the results.
I need the following measures:
- Base measures to calculate the sum of both columns
- A measure to calculate the last value for the Overtime corrected, as a stock measure
The first two are simple measures to sum up the columns.
The third is the following:
Overtime corrected =
VAR LastKnownDate = LASTNONBLANK('Date'[Date]
,[Overtime Corrected (Base)]
)
RETURN
CALCULATE([Overtime Corrected (Base)]
,LastKnownDate
)The measure [Overtime Corrected (Base)] shouldn’t be used in the report. Therefore, I set it as hidden.
These are the results:
Upon reviewing the results, you will see that they are correct, and the totals are also accurate.
This is the result I need to fulfill the requirements.
Conclusion
The challenge presented here is an example of calculating limits and ensuring that the running total is accurate. In addition, the corrections applied are also visible.
Additionally, it demonstrates that preparing the data can significantly simplify the measures in the data model.
For me, this is an essential aspect, as it improved efficiency and performance.
You can apply this approach to many other scenarios.
For example, you have a warehouse where you must ensure that the number of articles does not exceed a specific limit.
You can apply this approach to calculate the number of articles and determine if you need to reduce the number to ensure the warehouse is running correctly.
Later, while writing this piece, I realized that the SQL approach could have also been applied to the daily data. It wasn’t necessary to create a separate table.
I’m forced to create the table with the monthly data only if the calculation of the corrections must be applied to the monthly results.
Additionally, creating a separate table can be challenging, as you must consider all references to the dimensions involved.
But this is something that the business side must define. While I provided you with a possible solution, your role now is to decide how to translate it to your scenario.
I hope that you found this interesting.
Make sure to check out the other articles here on Towards Data Science.
References
The data is self-generated with fantasy names.
I generated this complete list by multiplying a list of First and last names with each other.
