Calculo de totales sin fines de semanas.

Vamos a trabajar con el modelo que suelo usar habitualmente, pero he quitado algunas columnas, tenemos ventas en diferentes provincias y fechas.

Queremos calcular los totales, pero quitando los fines de semana, es decir, los totales para los días hábiles por meses, y, años.

Tenemos ventas desde al año 2017 hasta el año 2021.

El modelo esta en formato de tabla, y, se llama ventas.

En los meses de marzo, y, julio del año 2017 no hubo ventas.

En el modelo podemos tener fechas repetidas, porque en el mismo día se pudo realizar diferentes ventas, además, no se realiza ventas el primer día del mes para algunas ocasiones, y, también para el ultimo día del mes, por lo que necesitamos una tabla de calendario, donde las fechas sean únicas.

Vamos a llevarnos el modelo a Power Pivot, para ello, con una celda dentro del modelo, vamos a la pestaña de Power Pivot, y, hacemos clic en agregar a modelo de datos.

Vemos que en la columna fecha, aparece la hora, pero no tenemos hora, por lo que vamos a la pestaña de inicio, vamos a tipo datos, desplegamos formato, y, seleccionamos:

Seleccionamos la columna de cantidad, y, total, y, la ponemos como numero entero.

Vamos a crear nuestra tabla de calendario, para ello, seleccionamos la columna de fecha, vamos a la pestaña diseñar, desplegamos tabla de fechas, y, seleccionamos nuevo.

Vemos que se ha creado una tabla llamada calendario, donde ha tomado las fechas del modelo, pero esta tabla comienza desde el día 01/01/2017 hasta el día 31/12/2021, haya o no haya habido ventas, además, las fechas son únicas.

Voy a agregar una columna personalizada, donde con el condicional IF, voy a preguntar que el día de la semana es diferente a sábado o domingo, para ello, usare el doble ampersand, que ponga día hábil, en caso contrario, que ponga día no hábil.

=IF(Calendario[Día de la semana]<>"sábado" && Calendario[Día de la semana]<>"domingo";"Dia habil";"Dia no habil")

Lo siguiente es relacionar ambas tablas, para ello, desde la pestaña de inicio, hacemos clic en vista de diagrama.

Vemos las dos tablas.

Vamos a arrastrar fecha de la tabla ventas a date de la tabla calendario, se creará una relación de uno a varios, donde el lado uno es date de la tabla calendario, porque las fechas son únicas, y, el lado varios es fecha de la tabla ventas, porque las fechas se pueden repetir.

Volvemos a la vista de datos.

Voy a crear una medida en la tabla ventas, donde con la función SUM, voy a calcular la suma de la columna total.

Totales:=SUM(Ventas[Total])

Vamos a la pestaña de inicio, desplegamos tabla dinámica, y, seleccionamos tabla dinámica.

En la ventana que se abre, seleccionamos nueva hoja de cálculo, y, aceptamos.

A filas, nos llevamos de la tabla calendario, año, y, mes, a valores nos llevamos la medida de totales.

Tenemos las ventas por mes y año.

Volvemos a Power Pivot, voy a crear otra medida, para que me haga un recuento de fechas de la tabla calendario, para ello, usare la función COUNTROWS.

Recuento fechas:=COUNTROWS(Calendario)

Volvemos a Excel, y, bajamos la medida a valores, y, vemos los días de cada mes, incluido sábados, y, domingos, y, también los meses que no tienen ventas.

Debemos de contar solo los días laborables, volvemos a Power Pivot, vamos a modificar la medida de recuento, donde voy a usar CALCULATE, como argumento expresión será COUNTROWS, y, como argumento filtro, ponemos la columna día hábil para que sea igual a día hábil.

Recuento fechas:=CALCULATE(COUNTROWS(Calendario);Calendario[Dias habiles]="Dia habil")

Vemos como el recuento ha cambiado.

Volvemos a la tabla dinámica, y, vemos los días hábiles para cada mes.

En este resultado están incluido todos los meses, aunque no haya habido ventas.

Ahora, vamos a crear una tabla, cosa que no podemos hacer en Power Pivot, pero lo haremos a través de una medida, una tabla donde vamos a unir el año y el mes de la tabla calendario, esto lo vamos a hacer con la función CROSSJOIN, esta función va a tomar dos tablas, y, multiplica cada fila:

Dias habile:=CROSSJOIN(

Como argumento tabla1, debemos de usar la función VALUES, que tomara cada valor único, aplicado a la columna año de la tabla calendario.

Dias habile:=CROSSJOIN(VALUES(Calendario[Year])

Punto y coma, como argumento tabla2, usamos de nuevo VALUES aplicado a la columna de mes de la tabla calendario.

Dias habile:=CROSSJOIN(VALUES(Calendario[Year]);VALUES(Calendario[Month]))

Ahora, envolvemos esta función en la función COUNTROWS.

Dias habile:=COUNTROWS(CROSSJOIN(VALUES(Calendario[Year]);VALUES(Calendario[Month])))

Vemos que nos devuelve 60, tenemos 5 años, que, multiplicado por 12 meses, nos devuelve 60.

Volvemos a la tabla dinámica, bajamos la medida a valores, y, vemos que aparece 1 para cada mes, siendo la suma de todos los 1 para un año de 12.

Ahora, debemos de hacer una fórmula para que no aparezcan los meses sin ventas, vemos que aparece marzo con 23 días hábiles, y, julio con 21 días hábiles, pero no tienen ventas.

Debemos de preguntar que, si el total es mayor a cero, es cuando debe de ejecutar la función CROSSJOIN, por lo que vamos a modificar la expresión, y, vamos a usar la función SUMX, donde como argumento tabla, es la expresión CROSSJOIN, y, como argumento filtro, con el condicional IF, voy a preguntar que si la medida totales es mayor a cero, que nos devuelva la medida recuento de fechas, omito el argumento valor si falso, y, si no hay coincidencia, nos pondrá un blanco.

Dias habile:=SUMX(

CROSSJOIN(VALUES(Calendario[Year]);

VALUES(Calendario[Month]));

IF([Totales]>0; [Recuento fechas]))

Creamos una nueva tabla dinámica, donde llevamos a filas, año y mes de la tabla calendario, a valores, llevamos la medida de totales, y, la medida de días hábiles.

Vemos que los meses que no tenían ventas ya no aparecen.

Si nos fijamos en el mes de enero, la ultima venta fue el día 29, y, hubo 8 días no laborables, por lo que el total de días a contar es de 9, pero en la tabla dinámica, aparecen 22 días, cosa que no es real.

En la tabla ventas, voy a añadir una columna calculada donde voy a calcular el final de mes de cada fecha, para ello, usare la función EOMONTH.

=EOMONTH(Ventas[Fecha];0)

Voy a modificar la medida días habile, donde voy a usar CALCULATE, como argumento expresión, será la función SUMX, y, como argumento filtro, usare la función FILTER, para filtrar la columna de fechas, siempre que sea menor o igual que la columna calculada.

Dias habile:=CALCULATE(

SUMX(

CROSSJOIN(VALUES(Calendario[Year]);

VALUES(Calendario[Month]));

IF([Totales]>0; [Recuento fechas]));

FILTER(Ventas;Ventas[Fecha]<=[Fin de mes]))

Volvemos a la tabla dinámica, y, ahora vemos solo los días de ventas, sin incluir los fines de semana.

Para comprobar que los resultados son correctos, he tomado diferentes meses, he quitado duplicados, he restado los fines de semana, y, el resultado es correcto