Introducción
uno de mis clientes me hizo la siguiente pregunta:
Mi empresa aplica un límite en el saldo de tiempo extra que cada empleado puede acumular. Este límite es de 20 horas en total.
Si un empleado supera este límite, se reducirán las horas extras superiores a las 20 horas.
¿Cómo puedo calcular esto en Power BI?
Esto suena como un trabajo fácil.
Agregue una medida que verifique si la cantidad total de horas extras excede los 20, y si es así, regrese 20. ¿No es correcto?
Probemos una medida DAX
Primero, debemos crear una medida que se agrega durante todo el tiempo extra realizado desde el comienzo del tiempo para calcular el saldo total:
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)
)En segundo lugar, creamos una medida para establecer el límite superior en 20:
Overtime corrected (try 1) =
VAR OvertimeOverall = [Overtime Overall]
RETURN
IF(OvertimeOverall > 20, 20, OvertimeOverall)Veamos el resultado:
Se ve correcto.
Tras un examen más detallado de los resultados, encontramos que no se suman correctamente.
Mira a Mandy Jones (se inventa el nombre):
A partir de abril, trabajó menos horas para reducir su equilibrio de horas extras.
Pero la medida [Overtime Overall] Todavía muestra más de 20 horas en abril, a pesar de que la cantidad se corrigió en marzo.
Esto no es correcto.
La medida [Overtime Overall] debe considerar las correcciones.
Además, el total está mal. Dado que el total siempre está por encima de 20, también mostrará 20.
Objetivo del cálculo
Antes de continuar, debemos definir los requisitos para determinar qué debe hacerse.
Primero, los resultados deben mostrarse solo a nivel mensual.
En segundo lugar, como se mencionó anteriormente, los resultados deben reflejar las correcciones realizadas en los meses anteriores.
Tercero, debemos poder ver las correcciones hechas para cada mes.
Prueba un enfoque DAX
Ok, ¿qué sucede cuando precalculamos una tabla para cada empleado en la medida y corrigimos los valores de acuerdo con él?
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])El filtro agregado con Calculatetable () es necesario para reducir el tamaño de la tabla generada con ResumenColumns ().
Como puede ver, los resultados son idénticos, ya que la medida aún no considera las correcciones en los meses anteriores.
Curiosamente, el total está en blanco.
La razón es que SelectionValue () no devuelve nada y, por lo tanto, no hay nada que calcular.
Puedo resolver esto usando VALORES():
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])Aquí está el resultado con el total correcto:
Pero este enfoque no escala bien, ya que la medida debe generar la tabla para todos los empleados durante el cálculo.
Está bien con 63 empleados, como lo han hecho mis datos, pero es una historia diferente con cientos o miles de empleados.
Sin embargo, el problema central sigue siendo: ¿cómo podemos calcular el resultado correcto para cada mes y cada empleado mientras mantenemos el total correcto?
Encontrar la solución
El enfoque para calcular los resultados correctos debe ser verificar las horas extras de cada mes, teniendo en cuenta cualquier corrección previa.
Para esto, el valor del mes anterior debe verificarse para ver si fue corregido.
El saldo de horas extras se puede actualizar con la cantidad de horas extras para el mes actual.
Esto implicaría un cálculo recursivo, donde el cálculo para cada fila usa el resultado de la misma columna del mes anterior.
Desafortunadamente, Dax no nos permite hacerlo, ya que lo considerará una dependencia circular:
Al regresar un paso, el enfoque podría ser desarrollarlo en la consulta de potencia.
No estoy seguro de si esto funcionaría, ya que requeriría un enfoque de procedimiento para procesar una fila a la vez.
Sé que en SQL, esto podría hacerse relativamente fácilmente.
Como la fuente de datos es una base de datos Azure SQL, decidí procesarla dentro de esa base de datos.
Calcule las correcciones en SQL
Teniendo en cuenta el requisito de que estos datos deben calcularse en un nivel mensual, creé una nueva tabla para almacenar los datos con las correcciones:
Aquí está todo el código SQL para calcular la corrección de cada empleado y cada mes:
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;La línea 39, donde calculo el total de ejecución, es interesante, ya que uso una técnica no tan bien conocida utilizando el ENCIMA() cláusula.
Independientemente de si usa T-SQL o no, puede usar este proceso para calcular los resultados necesarios con cualquier otro lenguaje de programación.
Estoy muy seguro de que habría podido escribir un método auto-recursivo para calcular los resultados necesarios sin el uso de un cursor.
Sin embargo, creo que este enfoque es más directo para adaptarse a otros idiomas.
Al mirar a Mandy Jones (Empid 1253), los resultados son los siguientes para 2025:
Al verificarlos, notará que las correcciones se aplican correctamente y el saldo de horas extras es correcta.
Integrando la tabla en Power BI
El último paso es integrar la nueva tabla en Power BI.
Simplemente puedo importar la tabla al modelo de datos y agregar relaciones a las otras tablas:
Ahora, puedo crear las medidas para calcular los resultados.
Necesito las siguientes medidas:
- Medidas base para calcular la suma de ambas columnas
- Una medida para calcular el último valor para las horas extras corregidas, como medida de stock
Las dos primeras son medidas simples para resumir las columnas.
El tercero es el siguiente:
Overtime corrected =
VAR LastKnownDate = LASTNONBLANK('Date'[Date]
,[Overtime Corrected (Base)]
)
RETURN
CALCULATE([Overtime Corrected (Base)]
,LastKnownDate
)La medida [Overtime Corrected (Base)] no debe usarse en el informe. Por lo tanto, lo configuré como oculto.
Estos son los resultados:
Al revisar los resultados, verá que son correctos y que los totales también son precisos.
Este es el resultado que necesito para cumplir con los requisitos.
Conclusión
El desafío presentado aquí es un ejemplo de cálculo de límites y garantizar que el total de funcionamiento sea preciso. Además, las correcciones aplicadas también son visibles.
Además, demuestra que preparar los datos puede simplificar significativamente las medidas en el modelo de datos.
Para mí, este es un aspecto esencial, ya que mejoró la eficiencia y el rendimiento.
Puede aplicar este enfoque a muchos otros escenarios.
Por ejemplo, tiene un almacén donde debe asegurarse de que el número de artículos no exceda un límite específico.
Puede aplicar este enfoque para calcular el número de artículos y determinar si necesita reducir el número para garantizar que el almacén esté funcionando correctamente.
Más tarde, mientras escribía esta pieza, me di cuenta de que el enfoque SQL también podría haberse aplicado a los datos diarios. No era necesario crear una tabla separada.
Me veo obligado a crear la tabla con los datos mensuales solo si el cálculo de las correcciones debe aplicarse a los resultados mensuales.
Además, crear una tabla separada puede ser un desafío, ya que debe considerar todas las referencias a las dimensiones involucradas.
Pero esto es algo que el lado comercial debe definir. Si bien le proporcioné una posible solución, su papel ahora es decidir cómo traducirlo a su escenario.
Espero que hayas encontrado esto interesante.
Asegúrese de consultar los otros artículos aquí en Data Science.
Referencias
Los datos se autogeneran con nombres de fantasía.
Generé esta lista completa multiplicando una lista de los primeros y apellidos entre sí.