Cómo crear una tabla de fechas en Microsoft Power BI

El artículo Cómo saber si la tabla de fechas automáticas es adecuada cuando se usa Power BI brinda una mirada al funcionamiento interno de cómo Microsoft Power BI maneja las fechas y las horas. Power BI crea una tabla de fechas interna para usted, pero no siempre producirá los requisitos de filtrado y agrupación que necesita. Cuando esto suceda, puede considerar crear la tabla de fechas usted mismo. Tener la habilidad de crear uno propio es una ventaja cuando se trabaja con un conjunto de datos complejo.

En este tutorial, le mostraré cómo crear una tabla de fechas cuando la tabla predeterminada interna no es adecuada usando Expresiones de análisis de datos. Si no está familiarizado con las tablas de fechas, le recomiendo que lea el artículo vinculado anterior antes de abordar la creación de una tabla de fechas usted mismo.

VER: Google Workspace frente a Microsoft 365: un análisis en paralelo con lista de verificación (TechRepublic Premium)

Estoy usando Microsoft Power BI Desktop en un sistema Windows 10 de 64 bits. A lo largo del artículo, usaré los términos tabla de fechas y tabla de hechos para describir la tabla de fechas y las tablas de datos, respectivamente.

Puede descargar el archivo de demostración de Microsoft Power BI para este tutorial.

¿Qué es una tabla de fechas en Power BI?

Una tabla de fechas es una tabla de fechas y otros metadatos sobre esas fechas. La relación entre las tablas de hechos y la tabla de fechas permite a los consumidores finales filtrar y comparar datos por períodos de tiempo, como meses y años.

Es mejor saber por adelantado si planea crear una tabla de fechas, porque Power BI solo permite una tabla de fechas. Si crea elementos visuales en la tabla de fechas automáticas interna y luego crea su propia tabla de fechas y la marca como tal, Power BI destruirá la tabla de fechas automáticas y todos los elementos visuales basados ​​en ella.

Una tabla de fechas se parece a cualquier otra tabla con una fila para cada fecha. La primera columna es una columna de tipo de datos de fecha/hora denominada Fecha. Las columnas restantes almacenan metadatos sobre cada fecha, como el año, el trimestre, el mes, etc., para la fecha, como puede ver en Figura A.

Figura A

Cuando utiliza una tabla de fechas personalizada, controla las jerarquías de fechas utilizadas por su modelo. Esto se filtra a las medidas rápidas y otras evaluaciones. Es difícil que una tabla de hechos cumpla con los requisitos para ser una tabla de fechas:

• La tabla de fechas debe tener una columna llamada Fecha que sea un tipo de datos de fecha/hora.
• La columna Fecha debe contener valores únicos.
• La columna Fecha no puede contener valores en blanco o nulos.
• La columna Fecha no puede tener fechas faltantes; las fechas deben ser contiguas.

Si elige usar una tabla de hechos, puede definirla como la tabla de fechas de la siguiente manera:

1. Seleccione la tabla en el panel Campos.
2. Haga clic con el botón derecho en la tabla y seleccione Marcar como tabla de fecha y luego seleccione Marcar como tabla de fecha en el menú resultante.

Este proceso puede resultar abrumador, pero si marca la tabla de hechos como una tabla de fechas, Power BI creará las relaciones y jerarquías en función de esta tabla. Si no lo hace, tendrá que crear las relaciones necesarias entre la tabla de fechas (hechos) y las otras tablas para obtener los mismos resultados.

Cómo usar DAX para crear una tabla de fechas en Power BI

Cuando necesite crear una tabla de fechas personalizada, puede usar DAX para crear una tabla calculada. DAX es un lenguaje de expresión utilizado en Analysis Services, Power BI y Power Pivot que incluye funciones y operadores.

Puede usar la función DAX CALENDAR o CALEDARAUTO para crear una tabla de fechas. Ambos devuelven una tabla de fechas de una sola columna.

Al considerar qué función usar, CALENDAR requiere la primera y la última fecha para poder generar una lista completa de fechas. CALENDARAUTO utiliza fechas existentes en una tabla de hechos para generar una lista de fechas. Usaremos CALENDARIO para crear una tabla de fechas.

La función CALENDARIO utiliza la siguiente sintaxis:

CALENDAR(<start_date>, <end_date>)

donde start_date es la primera fecha de la tabla de fechas resultante y end_date es la última. La función devolverá una tabla de una columna con una lista de fechas desde start_date hasta end_date y todos los días intermedios.

Ahora, usemos CALENDARIO para crear una tabla de fechas en Power BI usando el 1 de enero de 2000 como fecha_de_inicio y el 31 de diciembre de 2021 como :

1. Inicie Power BI. Si es necesario, elija Nuevo en el menú Archivo para trabajar con un nuevo archivo .pbix. No desea trabajar en un archivo .pbix existente.
2. Haga clic en Datos en el panel izquierdo.
3. En el extremo derecho, haga clic en la opción Escribir una expresión DAX para crear una nueva tabla en el menú.
4. Completa la expresión, Table = CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)) (Figura B).

Figura B

La función crea la nueva tabla, nombra la única columna Fecha y completa esa columna de manera apropiada. Vale la pena señalar que hay 1096 valores distintos o filas. Sin embargo, 365 * 3 es 1.095. Power BI sabe que 2020 fue un año bisiesto.

El siguiente paso es agregar columnas para cada componente de fecha que necesitará: número de semana, número de mes, trimestre, año, etc. En este punto, puede usar la opción Agregar columna para agregar más columnas. Sin embargo, es más eficiente agregarlos cuando crea la tabla:

Date =

ADDCOLUMNS (

CALENDAR (DATE (2020, 1, 1), DATE (2022, 12, 31)),

"Year", YEAR([Date]),

"MonthNumber", FORMAT([Date], "MM"),

"Quarter", FORMAT ([Date], "Q" ),

"DayOfWeek", FORMAT ([Date], "dddd" )

)

Figura C muestra los resultados. La cantidad de columnas que agrega depende de las necesidades de filtrado y agrupación de sus visualizaciones e informes.

Figura C

Para este ejemplo, la función crea solo unas pocas columnas, pero es posible que necesite muchas más. Para obtener una lista completa, visite Funciones de fecha y hora (DAX) – DAX | aprender.

Esta expresión combina ADDCOLUMNS y CALENDAR:

• Fecha es el nombre de la tabla resultante.
• ADDCOLUMNS le permite especificar columnas para Fecha.
• CALENDAR crea una tabla de fechas y completa la primera columna.
• Las siguientes cuatro líneas definen las columnas de metadatos.

En este punto, las cosas se ponen un poco turbias. Debe decidir si desea marcar la tabla personalizada como una tabla de fechas, como se explicó anteriormente en referencia a marcar una tabla de hechos como una tabla de fechas. Al hacerlo, se crearán las jerarquías personalizadas definidas por la tabla de fechas.

Por otro lado, si desea que Power BI haga esto por usted, no marque la tabla como una tabla de fechas. Puede crear las relaciones y usarlas para agrupar y filtrar de forma especializada. Esta es una de esas áreas donde no hay bien o mal, pero conocer sus datos es clave para tomar la decisión más eficiente.

Recuerde: cuando marca una tabla de hechos como una tabla de fechas, Power BI elimina la tabla de fechas automáticas integrada y cualquier elemento visual que haya creado anteriormente en ella. Si anula la selección de la tabla de fechas, Power BI creará automáticamente una nueva tabla de fechas automáticas.

Usar DAX para crear una tabla de fechas personalizada es fácil si sabe cómo hacerlo. Hay otras formas, y las cubriré en futuros artículos.