Crear una tabla de fechas
Durante la creación de informes en Power BI, un requisito empresarial frecuente es hacer cálculos basados en fecha y hora. Las organizaciones quieren conocer el comportamiento de tu empresa a lo largo de meses, trimestres, ejercicios, etc. Por esta razón, es fundamental que estos valores orientados al tiempo tengan el formato correcto. Power BI detecta automáticamente columnas y tablas de fechas; sin embargo, a veces necesitará tomar medidas adicionales para obtener las fechas en el formato que tu organización necesite.
Por ejemplo, imagina que vas a desarrollar informes para el equipo de ventas de la organización. La base de datos contiene tablas de ventas, pedidos, productos, etc. Como puedes ver, muchas de estas tablas, como Sales y Orders, contienen sus propias columnas de fecha, como muestran las columnas ShipDate y OrderDate en las tablas Sales y Orders. Se te pide que desarrolles una tabla con el total de ventas y los pedidos por año y mes. ¿Cómo puedes crear un objeto visual con varias tablas, y que cada una haga referencia a sus propias columnas de fecha?
Para solucionar este problema, puedes crear una tabla date común que se pueda usar en varias tablas. La siguiente sección explica cómo puedes lograr este tarea en Power BI.
Crear una tabla de fechas común
Puedes crear una tabla date común de estas maneras:
Datos de origen
DAX
Power Query
Datos de origen
En ocasiones, las bases de datos de origen y los almacenamientos de datos ya tienen sus propias tablas de fechas. Si el administrador que ha diseñado la base de datos ha realizado un trabajo exhaustivo, estas tablas se pueden usar para realizar las tareas siguientes:
Identificar los días festivos de la empresa
Separar el calendario y el año fiscal
Identificar fines de semana y días laborables
Las tablas de datos de origen están consolidadas y listas para su uso inmediato. Si tienes una tabla como tal, incorpórala a tu modelo semántico y no utilices ningún otro método que se describa en esta sección. Te recomendamos que utilices una tabla de fechas de origen, ya que es probable que se comparta con otras herramientas que puedas estar usando además de Power BI.
Si no tienes una tabla de datos de origen, puedes usar otras formas de crear una tabla de fechas común.
DAX
Puedes usar las funciones de Data Analysis Expression (DAX) CALENDARAUTO() o CALENDAR() para crear la tabla de fechas común. La función CALENDAR() devuelve un intervalo de fechas contiguo en función de unas fechas de inicio y de finalización que se especifican como argumentos en la función. Otra opción es la función CALENDARAUTO(), que devuelve un rango contiguo y completo de fechas determinadas automáticamente a partir de tu modelo semántico. La fecha de inicio se elige como la fecha más temprana que existe en tu modelo semántico y la fecha de finalización es la fecha más reciente, además de los datos que se han completado hasta el mes fiscal que puede incluir como argumento en la función CALENDARAUTO(). Para este ejemplo, se usa la función CALENDAR() porque solo quieres ver los datos desde el 31 de mayo de 2011 (el primer día en el que las ventas empezaron a realizar el seguimiento de estos datos) en adelante, durante los próximos 10 años.
En Power BI Desktop, selecciona Nueva tabla e introduce la siguiente fórmula DAX:
Dates = CALENDAR(DATE(2011, 5, 31), DATE(2022, 12, 31))
Ahora tienes una columna de fechas que puedes usar. Sin embargo, esta columna está ligeramente dispersa. También quieres ver columnas solo del año, el número del mes, la semana del año y el día de la semana. Puedes realizar esta tarea si seleccionas Nueva columna en la cinta y escribes la ecuación DAX siguiente, que recuperará el año de la tabla Date.
Year = YEAR(Dates[Date])
Puedes realizar el mismo proceso para recuperar el número del mes o el de la semana, así como el día de la semana:
MonthNum = MONTH(Dates[Date])
WeekNum = WEEKNUM(Dates[Date])
DayoftheWeek = FORMAT(Dates[Date], "DDDD")
Cuando hayas terminado, la tabla contendrá las columnas que se muestran en la figura siguiente.
Ya has creado una tabla de fechas común mediante DAX. Este proceso solo agrega tu nueva tabla al modelo semántico, aún necesitarás establecer relaciones entre tu tabla date y las tablas Sales y Order; luego, deberás marcar tu tabla como la tabla de fechas oficial de tu modelo semántico. Sin embargo, antes de completar esas tareas, debes pensar en otra forma de crear una tabla de fechas común: utilizar Power Query.
Power Query
Puedes utilizar el lenguaje M, el lenguaje de desarrollo que se utiliza para crear consultas en Power Query, para definir una tabla de fechas común.
Seleccione Transformar datos en Power BI Desktop, lo que te dirigirá a Power Query. En el espacio en blanco de la parte izquierda del panel Consultas, haz clic con el botón derecho para abrir el siguiente menú desplegable, donde seleccionarás Nueva consulta > Consulta en blanco.
En la vista Nueva consulta, escribe la siguiente fórmula M para crear una tabla de calendario:
= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0))
Para tus datos de ventas, deseas que la fecha de inicio refleje la fecha más temprana que tienes en tus datos: 31 de mayo de 2011. Además, quieres ver fechas de los próximos 10 años, incluidas las futuras. Gracias a esta estrategia, a medida que se reciban datos de nuevas ventas, no tendrás que volver a crear esta tabla. También puedes cambiar la duración. En este caso, quieres un punto de datos para cada día, pero también puedes incrementar en horas, minutos y segundos. La siguiente imagen muestra el resultado.
Tras realizar correctamente el proceso, verás que tiene una lista de fechas en lugar de una tabla de fechas. Para corregir este error, ve a la pestaña Transformar de la cinta y selecciona Convertir > A tabla. Como el nombre sugiere, esta característica convertirá la lista en una tabla. También puedes cambiar el nombre de la columna a DateCol.
A continuación, quieres agregar columnas a la nueva tabla para ver las fechas en términos de año, mes, semana y día, de modo que pueda crear una jerarquía en el objeto visual. La primera tarea consiste en cambiar el tipo de columna; para ello, selecciona el icono situado junto al nombre de la columna y, en el menú desplegable resultante, selecciona el tipo Fecha.
Cuando hayas terminado de seleccionar el tipo Fecha, puedes agregar columnas para el año, los meses, las semanas y los días. Ve a Agregar columna, selecciona el menú desplegable bajo Date y, después, selecciona Year, como se muestra en la imagen siguiente.
Como puedes ver, Power BI ha agregado una columna de todos los años extraídos de DateCol.
Completa el mismo proceso para los meses, las semanas y los días. Cuando hayas terminado el proceso, la tabla contendrá las columnas que se muestran en la figura siguiente.
Ya ha utilizado correctamente Power Query para crear una tabla de fechas común.
Los pasos anteriores muestran cómo introducir la tabla en el modelo semántico. Ahora, debes marcar la tabla como la tabla de fechas oficial, para que Power BI pueda reconocerla para todos los valores futuros y garantizar que el formato sea correcto.
Marcar como la tabla de fechas oficial
La primera tarea para marcar la tabla como la tabla de fechas oficial consiste en encontrarla en el panel Campos. Haz clic con el botón derecho en el nombre de la tabla y, después, selecciona Marcar como tabla de fechas, como se muestra en la imagen siguiente.
Al marcar la tabla como la tabla de fechas, Power BI hará validaciones para garantizar que los datos contengan cero valores nulos, sean únicos y contengan valores de fecha continuos durante un periodo. También puedes elegir columnas específicas de la tabla para marcarlas como la fecha, lo que puede resultar útil si tiene muchas columnas dentro de la tabla. Haz clic derecho en la tabla, selecciona Marcar como tabla de fechas y selecciona Configuración de la tabla de fechas. Aparecerá la siguiente ventana, donde podrás elegir qué columna debe marcarse como Date.
Al seleccionar Marcar como tabla de fechas, se quitarán las jerarquías generadas de forma automática del campo Date de la tabla que hayas marcado como tabla de fechas. Para otros campos de fecha, la jerarquía automática seguirá presente hasta que establezcas una relación entre ese campo y la tabla de fechas, o bien hasta que desactives la característica Fecha y hora automáticas. Puedes agregar manualmente una jerarquía a tu tabla de fechas común haciendo clic derecho en las columnas de year, month, week o day, en el panel Campos, y seleccionando Nueva jerarquía. Este proceso se analiza con más detalle posteriormente en este módulo.
Creación del objeto visual
Para crear el objeto visual entre las tablas Sales y Orders, tendrás que establecer una relación entre esta nueva tabla de fechas común y las tablas Sales y Orders. Como resultado, podrás crear objetos visuales mediante la nueva tabla de fechas. Para completar esta tarea, ve a la pestaña Modelo >Administrar relaciones, donde puedes crear relaciones entre la tabla de fechas común y las tablas Orders y Sales mediante la columna OrderDate. En la captura de pantalla siguiente se muestra un ejemplo de esa relación.
Después de construir las relaciones, puedes construir el objeto visual Ventas totales y cantidad de pedidos por tiempo con la tabla de fechas común que desarrolló utilizando el método DAX o Power Query.
Para determinar el total de ventas, debes agregar todas las ventas, porque la columna Amount de la tabla Sales solo examina los ingresos de cada venta, no los ingresos totales de ventas. Puedes completar esta tarea mediante el cálculo de la medida siguiente, que se explicará más adelante. El cálculo que se va a usar al compilar esta medida es el siguiente:
#Total Sales = SUM(Sales[‘Amount’])
Una vez que hayas terminado, puedes crear una tabla si vuelve a la pestaña Visualizaciones y seleccionar el objeto visual Tabla. Quieres ver el total de pedidos y ventas por año y mes, por lo que solo te interesa incluir las columnas Year y Month de la tabla date, la columna OrderQty y la medida #TotalSales. Cuando obtengas información sobre las jerarquías, también puedes crear una que te permitirá explorar en profundidad de años a meses. En este ejemplo, puedes verlos en paralelo. Ya has creado correctamente un objeto visual con una tabla de fechas común.
