DAX – Introducción – Parte 6 – Tabla Fecha

Cuando trabajamos con medidas de inteligencia de tiempo (Time Intelligence), es decir, cálculos donde el tiempo o fecha influye como filtro, o agrupador puede que aquellos que no vienen del mundo de BI, se pregunte que es eso de la tabla Fecha?, es necesaria?, siempre? o porque sin tabla Fecha pude hacer el calculo?

Cuando trabajamos con Power Pivot o Analysis Services es condición necesaria la tabla Fecha, en cambio en Power BI, no lo es, aunque es recomendable.

Vamos a analizar las dos opciones disponibles y sus características.

Multiples Columnas Fecha – Multiples Tablas Fecha

Cuando cargamos datos en Power BI, por cada columna tipo Fecha, Power BI construye automáticamente una tabla Fecha asociada, aunque no está visible de forma explicita.

A modo de ejemplo trabajaremos con un simple Power BI, de una única tabla, con dos columnas tipo Fecha y un importe.

DAX - Tabla Fecha - 1

En este caso, Power BI crea de forma automática dos tablas Fecha, una para cada campo, y la misma con un conjunto de campos y jerarquía.

Desde Power BI lo vemos como la figura siguiente

DAX - Tabla Fecha - 2.png

Si les interesa ver que arma mas internamente, podemos utilizar el Tabular Editor, y veremos las tablas, las sentencias DAX utilizadas y relaciones.

DAX - Tabla Fecha - 3.png

Ahora mas allá de lo que hace Power BI internamente cuando trabajamos con esta forma, debemos prestar atención a la forma de escribir nuestras sentencias DAX.

Tomando un caso sencillo de una medida que calcule el acumulado anual (YTD), si escribimos lo siguiente:

Sales YTD = TOTALYTD( SUM('BI Sales'[SalesAmount]), 'BI Sales'[OrderDate])

Veremos que si utilizamos la jerarquía de Power BI el calculo no funciona:

DAX - Tabla Fecha - 4

Pero si en lugar de la jerarquía, utilizamos el campo original, si DAX - Tabla Fecha - 5.png

Ahora para que funcione con la jerarquía agregar referencia a la columna Fecha de la tabla correspondiente, en este caso la formula quedaría

Sales YTD = TOTALYTD( SUM('BI Sales'[SalesAmount]), 'BI Sales'[OrderDate].[Date])

DAX - Tabla Fecha - 6.png

Ahora hemos resuelto el problema, pero si en lugar de la jerarquía referenciamos en la grilla la columna fecha, no veremos valores, lo que nuestro DAX, quizás deba manejar ambos casos.

Esto explica porque el asistente de medidas rápidas cuando arma la expresión le agrega un IF verificando estemos trabajando sobre la jerarquía.

SalesAmount YTD = 
IF(
    ISFILTERED('BI Sales'[OrderDate]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALYTD(SUM('BI Sales'[SalesAmount]), 'BI Sales'[OrderDate].[Date])
)

Otro tema molesto que tiene trabajar de esta forma es si queremos en un gráfico poner medidas de dos tablas, Ventas y Pedidos por ejemplo, sobre un eje de Calendario, o dos medidas que trabajan con dos fechas diferentes, en este caso mostrar totales por OrderDate y por DueDate.

Para estas situaciones es donde debemos utilizar la tabla Fecha

Multiples columnas Fecha – Una única tabla Fecha

Para crear una tabla fecha de forma explicita tenemos varios caminos:

  1. Importar la tabla Fecha de nuestro DW
  2. Crear una sentencia SQL que nos proporciones la misma
  3. Usando M
  4. Utilizando DAX

En lo personal prefiero los casos 1 y 4.

Para armar una tabla con DAX es muy sencillo, debemos seleccionar crear una tabla calculadas e ingresar una sentencia DAX sencilla

DAX - Tabla Fecha

La sentencia DAX “CALENDARAUTO”, toma las fechas mínimas y máximas de nuestras columnas tipo Fecha, y crear un tabla con años completos de esos rangos. Tambien podemos utilizar “CALENDA”, especificando inicio y fin.

Luego a esta tabla deberemos incorporar algunas columnas calculadas como Mes, MesNo, Año, etc., utilizando funciones FORMAR, YEAR, MONTH y aquellas que sean necesarias para nuestra tabla Fecha.

Luego debemos relacionar esta tabla con las columnas fechas

DAX - Tabla Fecha 2

y no olvidar el ingrediente mas importante, marcar la tabla como Fecha

DAX - Tabla Fecha 3DAX - Tabla Fecha 4

Luego solo debemos proceder a crear nuestras formulas utilizando esta tabla en lugar del campo fecha original

DAX - Tabla Fecha 5.png

 

 

 

 

 

 

 

 

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s