Calcular número de dias laborables entre dos fechas y tabla de festivos “variables”

Vamos a crear una función reutilizable que permita calcular el número de días laborables entre dos fechas. Para ello excluiremos fines de semana y los festivos que tenemos en una tabla de apoyo.

El caso surge de un proyecto desarrollado en una empresa de reparto de mercancías. Para cada expedición se dispone de varias fechas, la de expedición, la requerida de entrega, la real de entrega… Así hasta 8 fechas, y se desea el cálculo en dias entre casi todas ellas, excluyendo en todos los casos los fines de semana y los festivos que se han indicado en una tabla de apoyo.

La tabla “Festivos” que contiene algo de “veneno”… Hay festivos estatales, que son para todos los destinos, provinciales, para la provincia destino, y locales, que benefician a códigos postales concretos. En la siguiente imagen se puede ver:

Para nuestro caso resolver este problema con medidas de DAX resultaría excesivamente complejo. Pero si tu proyecto es tan sencillo como una diferencia entre dos fecha sin fines de semana o deseas explorar esta vía te sugiero que leas este post de Alberto Ferrari.

Yo lo trataré en Power Query creando una función con la que de un modo muy sencillo podremos crear todas las combinaciones de diferencias de días que necesitemos.

Requisitos de la función

La función requiere 3 parámetros de entrada: Fecha desde, Fecha Hasta y Código Postal Destino. Y devolverá un valor correspondiente al número de dias laborables según destino.

La rueda ya está inventada…

El problema del número de días laborables con tabla de festivos dista mucho de ser nuevo. De hecho, este post del TechNet de Microsoft es de 2016 y en el mismo encontré la función de partida que ha permitido desarrollar el proyecto del modo requerido.

La función en cuestión es la siguiente y podemos bautizarla como “DiasLaborables”:

let WorkingDays = (start as date, end as date, optional HDays as table) as number =>
   
let
  LstOfHolidays = if HDays = null then {} else Table.Column(HDays,"NameOfYourCalendarColumn"),
  NumLstOfHolidays = List.Transform(LstOfHolidays ,each Number.From(_)),
  LstOfDays = if start<end then {Number.From(start)..Number.From(end)} else {Number.From(end)..Number.From(start)},
  LstDiff  = List.Difference(LstOfDays, NumLstOfHolidays),
  LstMod = List.Transform(LstDiff, each Number.Mod(_, 7)),
  LstSel = List.Select(LstMod, each _>1) ,
  Result = if start<end then List.Count(LstSel) else List.Count(LstSel)*(-1)
in
  Result
in
  WorkingDays

La lógica de la función es muy ingeniosa y pasa por crear 2 listas o enumeraciones. La primera la que que contiene la lista de festivos y la segunda la secuencia de dias desde-hasta (líneas 5 y 6).

Para poder crear estas listas las fechas se deben convertir previamente a número y finalmente podremos sacar la diferencia entre ambas (línea 7).

La forma de quitar los fines de semana a la lista resultante se apoya en la función “Mod” o módulo, que devuelve el resto de una división, en este caso entre 7 (línea 8). Si el resto es 0 o 1 es sábado y domingo y filtramos de la lista para que muestre solo los que son mayores de 1 (línea 9).

El resultado final es el recuento de líneas de la lista resultante (línea 10)… Una maravilla.

Para la mayoría de los casos será suficiente, y si los sábados fueran laborables, con pocos cambios se adaptaría fácilmente sin fastidiar nuestro modelo si ya hemos hecho uso de la función.

Modificando la función para incorporar nuestros festivos

Pero en nuestro caso se queda corta por que los festivos varían según el ámbito y destino. O sea, los festivos son una lista variable según el destino. Vamos a ver cómo lo resolvemos.

Si observamos la función anterior, la lógica que devuelve la lista de festivos está en la líneas 4 y 5, que “abren” la tabla de fechas pasada como parámetro y la convierten a lista numérica respectivamente. Bastaría con cambiar la línea 4 para que cargue nuestra lista variable de festivos.

Esto lo conseguiremos con otra función que recibirá como parámetro el código postal y devolverá su lista de festivos. Pero ojo, por encima tenemos festivos provinciales y además los estatales. Y los festivos pueden caer en fin de semana y debería ignorarlos.

Esta sería la función:

(cp as text) as table=>
let
Source = Festivos,
cpp=cp,
//cpp="12165",
FirstCharacters = Text.Start(cpp, 2),
#"Filtered Rows" = Table.SelectRows(#"Source", each ([CODIGO_POSTAL] =FirstCharacters or [CODIGO_POSTAL] = cpp or [CODIGO_POSTAL] = "TODOS")),
#"Filtered Rows2"= Table.SelectRows(#"Filtered Rows", each ([Day of Week] <> 6)),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows2",{"FECHA"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")
in
#"Removed Duplicates"

Como parámetro se le pasa el código postal y el resultado es una tabla. Del código postal extraeremos los dos primeros caracteres que identifican a la provincia (línea 7). En la línea 9 filtramos por todos los casos: Provincia, CP y TODOS.

En la línea 10 excluyo los días de la semana que no deben estar, especificados en la columna “Day of Week”.

La fila 11 indica que quitamos todas las columnas salvo la de fecha y en la fila 12 hacemos una agrupación para que no haya duplicados.

Si invocamos a esta función para un código postal obtenemos este resultado: Los festivos estatales, los de su provincia y los locales.

Si volvemos a nuestra función DiasLaborables y hacemos el cambio lograremos nuestro propósito:

También hemos sustituido el tercer parámetro de la función por el código postal y que a su vez se pasa a la función GetFestivos.

El código final:

let WorkingDays = (start as date, end as date, cp as text) as number =>
let
LstOfHolidays=Table.Column( GetFestivos(cp) ,"FECHA"),
NumLstOfHolidays = List.Transform(LstOfHolidays ,each Number.From(_)),
LstOfDays = if start1) ,
Result = if start<end then List.Count(LstSel) else List.Count(LstSel)*(-1)
in
Result
in
WorkingDays

Si probamos nuestra función veremos que funciona de acuerdo a lo esperado:

Es momento de usarla en nuestro proyecto

Para ello vamos a tabla con expediciones añadiremos una nueva columna invocando a función personalizada:

En la misma hemos de indicar como parámetros las columnas que correspondan al cálculo para cada línea:

Y con unas pocas llamadas agregaremos las columnas con los cálculos buscados:

Optimización

La solución está bien pero en caso de tener que hacer el cálculo con varias columnas y un volumen de datos grande puede que no sea eficiente. La función GetDiasLaborables va a ejecutar la función Getfestivos tantas veces por línea como en columnas sea invocada. Si vamos a calcular 8 columnas será invocada 8 veces por línea… Mala cosa, pero podemos mejorar el proceso.

Independientemente del número de columnas a calcular , GetFestivos sólo necesitamos que sea ejecutado una vez por línea. Podemos insertar un paso que haga esta llamada, invocando la función:

Esto añadirá una columna con la tabla de festivos resultante para cada línea:

Recuerda que la función primera “DiasLaborables”, esa que copiamos y que modificamos, recibía como tercer parámetro una tabla de festivos… ¿Y si la invocamos pasando como tercer parámetro la columna que contiene esa tabla y que acabamos de añadir?:

Cuando hayamos agregado las columnas con el cálculo de Dias Laborables podremos prescindir de la columna tipo tabla y la podremos quitar.

Espero que te haya sido de ayuda.

Recursos adicionales de interés:

Number.Mod – PowerQuery M | Microsoft Docs

Excluding weekends and holidays in date differences in PowerApps | Microsoft Power Apps

Calculate Working Days using Power Query (microsoft.com)