El misterioso caso de las fechas cambiantes

Todo empezó cuando un colega en el trabajo copió una tabla de fechas de un cuaderno Excel a otro. Era uno de esos raros días en que su atención esta centrada más en el trabajo que en cosas agradables.
Con sorpresa observó que las fechas cambiaban al ser copiadas de un cuaderno al otro. Esta es la situación (por supuesto que no se trataba de fechas de nacimiento)

Después de rascarse repetidamente el cuero cabelludo, acción que, está por demás decir, no incrementa la capacidad analítica del rascado, decidió consultarme.

Debo reconocer que tuve unos momentos de embarazosa vacilación hasta dar con la clave del misterio. Excel tiene dos bases distintas para los cálculos de fecha: 1900 y 1904.

Como (casi) todos sabemos Excel usa una serie de números enteros para representar las fechas. La base por defecto (para todas las versiones de Excel para Windows) es el 1 de enero del 1900, fecha representada por el número 1. El número 2 representa el 2 de enero de 1900 y así sucesivamente.
Pero sucede que Excel permite también usar como base el año 1904. De manera que el  mismo número de serie puede representar dos fechas distintas dependiendo de las definiciones del cuaderno Excel.
De esta manera, al copiar las fechas Excel "traduce" el número de serie a distintas fechas.
En la imagen de arriba podemos ver que la diferencia entre las fechas en los cuadernos es de 4 años; más precisamente 1462 días.

Cambiamos la base en Archivo-Opciones-Avanzadas-Al calcular este libro

En esta captura de pantalla podemos ver cambian las fechas al cambiar el año-base




Volviendo al caso de mi colega, ¿cómo remediar esta situación?

Una posibilidad es cambiar la definición en el cuaderno de mi colega. Esta solución no es recomendable ya que si hay otras fechas en el cuaderno, éstas cambiaran también.
Otra posibilidad es poner el número 1462 en alguna celda vacía y copiarla; luego seleccionar el rango de fechas a modificar y usar Pegado Especial-Valores-Restar para corregir la fechas.

¿Por qué conviven estas dos bases en Excel? Aparentemente por motivos de compatibilidad con las versiones de Excel para Mac. En "Mac" la base es 1904 (la primer versión de Excel fue para Mac).

Como no fuera esta dualidad es problemática y puede conducir a errores importantes.

Total Anual Móvil con PowerPivot - versión mejorada

En el post anterior mostré como calcular el TAM (Total Anual Móvil) con PowerPivot. La solución tiene algunos problemas:

1. no muestra los totales para cada año;
2. el cálculo del TAM promedio es inexacto para el primer año (2015 en nuestro ejemplo).

Aparentemente podemos solucionar el primer problema con facilidad; sencillamente agregamos los subtotales para el campo Year (el campo que arrastramos al área de las filas), pero entonces nos topamos con un nuevo problema

Total Móvil Anual (TAM) con PowerPivot

Como ya he declarado alguna vez en el pasado, lo prometido es deuda. A un poco más de dos meses de haber publicado el segundo post sobre como calcular el total anual móvil donde prometí mostar como calcularlo con PowerPivot, paso a saldar mi deuda (mis pocos pero buenos lectores sabrán disculpar).

PowerPivot es imbatible en el área de cálculos que impliquen comparación de períodos (lo que en inglés se da por llamar "Time Intelligence"). Y lo vamos a mostrar con el tema que estamos tratando, el cálculo del total anual móvil.

En este post parto de la suposición que el lector tiene un  conocimiento adecuado de PowerPivot y en particular del lenguaje DAX, y si no es así, espero que esta nota sirva de aliciente para impulsarlo a estudiar estas herramientas.

Vamos al grano. Para este ejemplo vamos a usar una tabla de datos diarios (ventas) del período 2015-2018.

Nuestra tarea es calcular el total anual móvil (TAM) para cada mes, es decir, totalizar el total mensual y compararlo con el acumulado de los últimos doce meses. Luego podemos calcular el promedio mensual del TAM y compararlo con las ventas del mes.