Tutorial Power BI: Crear tabla de fechas (4 alternativas)

Un tema recurrente en las consultas que me hacéis son las tablas de tiempo o tablas de fecha ( Date Table en inglés) en Power BI. ¿Es importante crear una tabla de fechas en nuestros modelos de datos? ¿Cuál es la mejor manera de crearlas? Por ello, hoy os traemos este tutorial con 4 alternativas diferentes para crear una tabla de tiempo.

Primera Pregunta: ¿Es necesario una tabla de fechas independiente?

Definitivamente SÍ. En este primer apartado os contaremos porqué es importante disponer de esta tabla de tiempo en la mayoría de los casos a los que os enfrentéis en Power BI (y en realidad en cualquier modelo de datos con cualquier herramienta de análisis).

Es cierto que la funcionalidad de Inteligencia de Tiempo (Auto Date-Time en inglés) permite que cualquier Campo Fecha se convierta en una jerarquía de tiempo o “mini tabla de tiempo oculta”:

Podríamos pensar que con esta opción activa (se activa por defecto) ya tendríamos la jerarquía de fechas solucionada y no necesitaríamos una tabla específica de fechas, pero no es así.

Razones por las que necesitamos una tabla de fechas:

• Relaciones de tablas en base a fechas: En muchas ocasiones tenemos varias tablas de hechos a analizar y sus fechas tienen relación. Imaginad que estamos analizando las ventas en una tabla y las visitas comerciales en otra. Necesitaremos una tabla de fechas independiente para poder analizar la relación entre ventas y visitas en un periodo de tiempo común.
• Filtro o slicer de tiempo común: Igual que en el ejemplo anterior, si tenemos varias tablas de hechos necesitaremos usar un filtro de fechas que filtre todas las tablas a la vez.
• Añadir más atributos de fecha: El Auto Date-Time de Power BI crea una jerarquía Año-Trimestre-Mes-Fecha, que en muchos casos es suficiente. Pero, ¿y si necesitamos analizar los datos por semana? ¿Y si quiero estudiar si el día de la semana tiene influencia en las métricas? Necesitaré una tabla de tiempo personalizada.
• Uso de Fórmulas de Tiempo DAX: al usar fórmulas de tiempo en DAX es recomendable tener una tabla de fechas independiente para su correcto funcionamiento. En casos como SAMEPERIODLASTYEAR o similares si hacemos el filtro de tiempo directamente en la tabla de hechos seguramente no os devolverá nada, ya que no puede mirar el año anterior si está filtrada por el año actual.
• Características de negocio en tabla fechas: En muchos sectores el año natural (enero-Diciembre) no coincide con su ejercicio de negocio (en USA es habitual que el ejercicio sea de Julio a Junio por ejemplo). Aparte puede que tenga mucha relevancia los festivos nacionales y locales (como en Retail). Toda esta información debería estar en tu tabla de tiempo, y con Auto Date-Time no la tendrás.

Creo que ya hemos enumerado suficientes razones para que os hagáis una idea de la importancia de una tabla de tiempo específica en vuestros modelos de datos. ;)

Por ello, el primer paso debería ser desactivar la opción de Auto-Date Time para que no nos cree esa jerarquía automática en los campos fecha. Para ello debéis ir a Archivo > Opciones y Configuración > Opciones y desactivar este check en la sección de Carga de Datos:

4 alternativas para crear tu tabla de tiempo

Como he comentado al inicio de este Tutorial, os mostraré 4 formas de crear una tabla de fechas independiente:

• Usar una tabla de fechas del origen de datos
• Crear una tabla de tiempo con DAX
• Crear una tabla de tiempo con Power Query
• Crear una tabla de tiempo con la funcionalidad “Especificar Datos”

Seguramente he ordenado la lista según la opción que yo elegiría (excepto las opciones DAX y Power Query, en la que he puesto Power Query en segundo lugar porque es más complejo inicialmente, aunque tiene algunas ventajas que comentaremos).

WARNING: Si alguno estaba pensando en crear su tabla de tiempo haciendo una copia de la tabla de hechos, dejando sólo el campo fecha y filtrando duplicados, no funcionará. Es necesario que la tabla de fechas contenga todas las fechas del periodo, por lo que si existen fechas donde no se producen hechos, esa tabla no funcionará como tabla de tiempo.

1. Usar una tabla de fechas del origen de datos

Sin lugar a dudas, si disponéis de una tabla de fechas, ejercicios, calendario u horarios en vuestro origen de datos, usadla.

En muchas ocasiones tenemos como origen un sistema de gestión como un ERP, TPV, CRM, etc. Estas soluciones suelen tener una tabla de tiempo que incluye los festivos, horarios de trabajo, fiestas locales, etc. Os ahorraréis crear estos datos y evitaréis que el usuario tenga que mantener la tabla de fechas en los 2 sistemas.

2. Crear una tabla de tiempo con DAX

Una opción muy habitual es crear la tabla con DAX. En este caso, importamos los datos de los orígenes necesarios y creamos la tabla de tiempo con DAX a través de la opción “Nueva Tabla“:

Para crear nuestra tabla de tiempo, pulsamos en la opción de menú Modelado > Nueva Tabla y veréis que se activa el editor de código DAX. En este editor debéis introducir el código de alguna de las 2 opciones que veremos a continuación:

FUNCIÓN DAX CALENDAR()

La función CALENDAR() es la más usada para crear una tabla de fechas con DAX. El código sería similar a este:

Tabla Fechas DAX CALENDAR() = 
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"Id Fecha", FORMAT ( [Date], "YYYYMMDD" ),
"Año", YEAR ( [Date] ),
"Mes", FORMAT ( [Date], "MM" ),
"Periodo", FORMAT ( [Date], "YYYY/MM" ),
"Periodo2", FORMAT ( [Date], "YYYY/mmm" ),
"Nombre Corto Mes", FORMAT ( [Date], "mmm" ),
"Nombre Mes", FORMAT ( [Date], "mmmm" ),
"Día Semana número", WEEKDAY ( [Date] ),
"Día Semana ", FORMAT ( [Date], "dddd" ),
"Día Semana Corto", FORMAT ( [Date], "ddd" ),
"Trimestre", "Q" & FORMAT ( [Date], "Q" ),
"Año/trimestre", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

Como veis, Se crea la tabla a través de la función CALENDAR (indicando una fecha de inicio y de fin) y añadiendo las columnas con los atributos que queramos (mes, año, semana, trimestre,..)

FUNCIÓN DAX CALENDARAUTO()

En este caso no podemos crear la tabla de fechas inicialmente, ya que la función CALENDARAUTO() detecta los campos tipo Fecha de otras tablas para crear la tabla de fechas:

DateTable DAX CalendarAuto =
VAR MinYear = YEAR ( MIN ( OtraTabla[Date] ) )
VAR MaxYear = YEAR ( MAX ( OtraTabla[Date] ) )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "Month Number", MONTH ( [Date] )
)

En este código debéis sustituir OtraTabla[Date] por el campo Fecha de vuestra tabla de hechos del informe. En el ejemplo que hemos creado para compartir con los suscritos al blog veréis que hemos sustituido OtraTabla[Date] por el campo Ventas[OrderDate] (nuestra tabla de hechos).

Lo bueno de CalendarAuto es que se adapta a las fechas de nuestra tabla de hechos.

3. Crear una tabla de tiempo con Power Query

El uso de DAX implica que la tabla de tiempo se crea posteriormente a la importación de datos. En el caso de Power Query, la tabla se crea durante la importación de datos.

La nomenclatura de Power Query es menos conocida, por eso la he puesto en tercer lugar, aunque crear la tabla fechas durante la actualización de datos tiene sus ventajas. Por ejemplo, podamos realizar esta carga en un dataflow y reaprovecharlo en todos nuestros informes.

El mejor código Power Query que he visto es del crack Reza Rad, así que os dejo un link al post original. Ese código de Power Query es el que he utilizado en el pbix de ejemplo de este tutorial disponible para los suscriptores de vandalytic.

4. Crear una tabla de tiempo con la funcionalidad “Especificar Datos”

Esta es la opción más simple, pero también la que más limitaciones tiene. Para usarla debemos usar la opción “Especificar Datos”.

En un primer paso, creamos la tabla de de Fechas en Excel por ejemplo:

Luego usamos el botón “Especificar Datos” y pegamos la tabla de Excel en la ventana emergente:

Esta opción tiene limitaciones, ya que sólo pueden contener 3000 celdas en total:

Como en cada tutorial de Power BI, os dejo un panel de ejemplo con las formas de crear tablas de fecha que podréis descargar si estáis suscritos al blog (es gratis):

Conclusiones

No es complicado crear tablas de fechas, pero sí que es muy importante para que el modelo de datos sea correcto y funcione a la perfección.

Si necesitas crear varias tablas fecha, quizás la opción Power Query sea la mejor ya que en el editor puedes copiar tablas y cambiarlas de nombre.

Espero que esta recopilación de formas de crear tablas de tiempo os haya ayudado. Si tenéis alguna otra manera de crear tablas de fecha, podéis compartirlas en los comentarios de este post.

Recordad que si os suscribís tendréis acceso al fichero pbix de ejemplo de este tutorial para ver en detalle el uso de las fórmulas y técncias explicadas.

¡Gracias por seguirnos!