Introduction
, considered one of my purchasers requested me the next query:
My firm applies a restrict on the extra time stability every worker can accumulate. This Restrict is at 20 hours in whole.
If an worker surpasses this restrict, extra time exceeding 20 hours will likely be decreased.
How can I calculate this in Energy BI?
This feels like a simple job.
Add a measure that checks if the whole quantity of extra time exceeds 20, and if that’s the case, return 20. Isn’t that appropriate?
Let’s attempt a DAX measure
First, we should create a measure that provides up all extra time executed for the reason that starting of time to calculate the whole stability:
Additional time Total =
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 higher restrict to twenty:
Additional time corrected (attempt 1) =
VAR OvertimeOverall = [Overtime Overall]
RETURN
IF(OvertimeOverall > 20, 20, OvertimeOverall)Let’s see the outcome:
It appears to be like appropriate.
Upon nearer examination of the outcomes, we discover that they don’t add up appropriately.
Take a look at Mandy Jones (Title is invented):
Ranging from April, she labored fewer hours to scale back her extra time stability.
However the measure [Overtime Overall] nonetheless exhibits greater than 20 hours in April, though the quantity was corrected in March.
This isn’t appropriate.
The measure [Overtime Overall] should think about the corrections.
Furthermore, the whole is unsuitable. Because the whole is all the time above 20, it’ll additionally show 20.
Goal of the calculation
Earlier than we proceed, we should outline the necessities to find out what must be executed.
First, the outcomes should be proven solely on the month-to-month degree.
Second, as talked about above, the outcomes should mirror the corrections made within the previous month(s).
Third, we should be capable to see the corrections made for every month.
Attempt a DAX strategy
OK, what occurs once we precalculate a desk for every Worker within the measure and proper the values in line with it:
Additional time Corrected (attempt 2) =
VAR ActEmpId = SELECTEDVALUE('Additional time'[EmployeeID])
VAR EmpsByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Additional time'[EmployeeID]
,'Date'[Last Day Of Month]
,"OvertimeCorrected", IF([Overtime Overall] > 20.0, 20.0, [Overtime Overall])
)
,'Additional time'[EmployeeID] = ActEmpId
)
RETURN
SUMX(EmpsByMonth, [OvertimeCorrected])The filter added with CALCULATETABLE() is important to scale back the scale of the desk generated with SUMMARIZECOLUMNS().
As you’ll be able to see, the outcomes are similar, because the measure nonetheless doesn’t think about the corrections within the earlier months.
Curiously, the whole is clean.
The reason being that SELECTEDVALUE() doesn’t return something, and thus, there’s nothing to calculate.
I can resolve this through the use of VALUES():
Additional time Corrected (attempt 2) =
VAR ActEmpId = VALUES('Additional time'[EmployeeID])
VAR EmpsByMonth =
CALCULATETABLE(
SUMMARIZECOLUMNS(
'Additional time'[EmployeeID]
,'Date'[Last Day Of Month]
,"OvertimeCorrected", IF([Overtime Overall] > 20.0, 20.0, [Overtime Overall])
)
,'Additional time'[EmployeeID] IN ActEmpId
)
RETURN
SUMX(EmpsByMonth, [OvertimeCorrected])Right here is the outcome with the right whole:
However this strategy doesn’t scale effectively, because the measure should generate the desk for all workers through the calculation.
It’s OK with 63 workers, as my knowledge has, but it surely’s a distinct story with lots of or 1000’s of workers.
Nevertheless, the core situation stays: How can we calculate the right outcome for every month and every worker whereas sustaining the right whole?
Discovering the answer
The strategy to calculate the right outcomes needs to be to verify the extra time for every month, bearing in mind any earlier corrections.
For this, the worth of the earlier month should be checked to see if it was corrected.
The extra time stability can then be up to date with the quantity of extra time for the present month.
This may contain a recursive calculation, the place the calculation for every row makes use of the results of the identical column from the earlier month.
Sadly, DAX doesn’t enable us to do it, as it’ll think about it a round dependency:
When going again one step, the strategy could possibly be to develop it in Energy Question.
I’m not sure if this could work, as it might require a procedural strategy to course of one row at a time.
I do know that in SQL, this could possibly be executed comparatively simply.
As the information supply is an Azure SQL database, I made a decision to course of it inside that database.
Calculate the corrections in SQL
Considering the requirement that this knowledge must be calculated on a month-to-month degree, I created a brand new desk to retailer the information with the corrections:
Right here is the complete SQL code to calculate the correction for every worker and every 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, the place I calculate the operating whole, is fascinating, as I exploit a not-so-well-known approach utilizing the OVER() clause.
No matter whether or not you utilize T-SQL or not, you should use this course of to calculate the wanted outcomes with every other programming language.
I’m very assured that I might have been capable of write a self-recursive technique to calculate the wanted outcomes with out using a cursor.
Nevertheless, I imagine this strategy is extra simple to adapt to different languages.
When Mandy Jones (EmpID 1253), the outcomes are the next for 2025:
When checking them, you’ll discover that the corrections are utilized appropriately and the Additional time stability is appropriate.
Integrating the desk in Energy BI
The final step is to combine the brand new desk into Energy BI.
I can merely import the desk into the information mannequin and add Relationships to the opposite tables:
Now, I can create the measures to calculate the outcomes.
I want the next measures:
- Base measures to calculate the sum of each columns
- A measure to calculate the final worth for the Additional time corrected, as a inventory measure
The primary two are easy measures to sum up the columns.
The third is the next:
Additional time corrected =
VAR LastKnownDate = LASTNONBLANK('Date'[Date]
,[Overtime Corrected (Base)]
)
RETURN
CALCULATE([Overtime Corrected (Base)]
,LastKnownDate
)The measure [Overtime Corrected (Base)] shouldn’t be used within the report. Subsequently, I set it as hidden.
These are the outcomes:
Upon reviewing the outcomes, you will notice that they’re appropriate, and the totals are additionally correct.
That is the outcome I want to satisfy the necessities.
Conclusion
The problem offered right here is an instance of calculating limits and making certain that the operating whole is correct. As well as, the corrections utilized are additionally seen.
Moreover, it demonstrates that making ready the information can considerably simplify the measures within the knowledge mannequin.
For me, that is a vital side, because it improved effectivity and efficiency.
You’ll be able to apply this strategy to many different eventualities.
For instance, you may have a warehouse the place you will need to make sure that the variety of articles doesn’t exceed a particular restrict.
You’ll be able to apply this strategy to calculate the variety of articles and decide if you must scale back the quantity to make sure the warehouse is operating appropriately.
Later, whereas penning this piece, I spotted that the SQL strategy may have additionally been utilized to the every day knowledge. It wasn’t essential to create a separate desk.
I’m compelled to create the desk with the month-to-month knowledge provided that the calculation of the corrections should be utilized to the month-to-month outcomes.
Moreover, making a separate desk could be difficult, as you will need to think about all references to the size concerned.
However that is one thing that the enterprise facet should outline. Whereas I supplied you with a attainable resolution, your position now could be to determine tips on how to translate it to your situation.
I hope that you just discovered this fascinating.
Be sure to take a look at the opposite articles right here on In the direction of Information Science.
References
The info is self-generated with fantasy names.
I generated this whole checklist by multiplying a listing of First and final names with one another.
