Saltar al contenido principal
Power BI

Tablas de calendario de Power BI

Nivel: principiantes

Publicado por primera vez en mayo de 2015, actualizado en febrero de 2021. Muchos aspectos de este artículo también se aplican a Power Pivot para Excel.

Técnicamente, no necesita una tabla de calendario para usar Power BI. Power BI viene con una capacidad de fecha incorporada llamada “Inteligencia de tiempo automática”. Pero la capacidad incorporada es muy básica y, lo que es peor, tiene algunos efectos secundarios negativos. En este artículo cubro lo que sale de la caja y el enfoque preferido para construir su propia mesa de calendario.

Inteligencia de tiempo automática

La inteligencia de tiempo automática es la forma en que Microsoft facilita la “acumulación” de fechas en los conceptos de meses, trimestres y años. Esta función está activada de forma predeterminada. Puede buscar (y cambiar) la configuración abriendo Power BI Desktop y navegando a Archivo Opciones y Configuración Opciones como se muestra a continuación. En la sección Global Data Load (que se muestra a continuación) puede activar o desactivar esta función de forma permanente.

auto-ti-8320435

También puede navegar a Archivo actual Carga de datos y activarlo / desactivarlo solo para el workbook activo (como se muestra a continuación).

auto-ti-for-file-9434340

La inteligencia de tiempo automático es muy básica en sus capacidades y tiene una serie de problemas.

  • Solo funciona en años calendario (no años fiscales, a menos que tenga la suerte de que su año fiscal sea el año calendario).
  • Siempre está en el nivel de granularidad del día.
  • Solo se acumula de días a meses, trimestres y años. No se manejan semanas, trimestres, calendarios ISO (445) o cualquier otro período de tiempo específico de la empresa que pueda ser necesario.
  • Crea 1 tabla de calendario oculta para cada columna de fecha en su informe. Este hecho puede hacer que el tamaño de su libro crezca mucho más de lo que sería de otra manera.
  • Cada tabla de calendario es independiente; no hay manera de modelo una sola segmentación para filtrar varias columnas de fecha (aunque esto se puede hacer en la interfaz de usuario agrupando segmentaciones).

Esta inteligencia de tiempo automático está realmente dirigida a principiantes absolutos que no quieren aprender a modelar sus propios datos. Mi opinión es que debería desactivar esta función y, en su lugar, crear su propia tabla de calendario.

Razones para usar una tabla de calendario de Power BI

Hay muchas buenas razones para crear su propia tabla de calendario. Construir su propia mesa de calendario le permite:

  • Utilice el poder de filtrado de Power BI para hacer que sus informes sean rápidos y ágiles al acceder a las columnas de datos relevantes que forman parte de la tabla del calendario.
  • Filtre sus informes por atributos como Año, Mes, Trimestre en tantas tablas de datos como necesite (por ejemplo, Ventas reales, Ventas presupuestadas, Existencias disponibles), así como cualquier otra agregación de tiempo que necesite para su negocio.
  • cree su propia vista personalizada del tiempo que puede ser diferente a un calendario estándar (como un calendario 4/4/5) y / o cree su propia definición de lo que es un año financiero.
  • crear tablas de calendario para períodos de tiempo que no están en el nivel de “día”, por ejemplo, mes, año, minuto, etc.
  • Construya conceptos como una tabla de calendario de inteligencia de tiempo. Se pueden utilizar para permitir que un usuario seleccione un horizonte de tiempo de una segmentación (por ejemplo, Hoy, Ayer, Esta semana, El año pasado) y hacer que el informe se actualice automáticamente para reflejar ese período de tiempo.

En algunas situaciones, simplemente debe tener una tabla de calendario:

  • si desea utilizar algunas de las funciones de inteligencia de tiempo incorporadas más avanzadas en DAX (como ventas continuas de 13 semanas).
  • cargar e informar sobre datos que no se encuentran en el nivel de granularidad del día.

Si carga una tabla de calendario “estándar” (reglas a continuación), puede utilizar las funciones de inteligencia de tiempo incorporadas. Hay otras formas de crear fórmulas de inteligencia de tiempo además de las funciones incorporadas, pero en general, el DAX es un poco más complejo. Más sobre eso más tarde.

super-charge-power-bi-ad_2-1024x128-2704066

Reglas para una tabla de calendario estándar

Aquí están las reglas para construir una tabla de calendario estándar conforme. Debes seguir estas reglas si desea utilizar funciones de inteligencia de tiempo. No tienes que seguir estas reglas si no necesita / desea utilizar las funciones de inteligencia de tiempo incorporadas (pero sigue siendo una buena práctica, especialmente si sus datos están en un “nivel de día” de granularidad).

  • Debe tener una columna de fecha en su tabla de calendario
  • Las fechas en esta columna deben estar en un rango contiguo que cubra el período completo de sus datos desde el primer día del primer año de sus datos hasta el último día del último año de sus datos:
    • no faltan fechas. No importa si no trabaja los fines de semana, DEBE incluir TODAS las fechas en la tabla del calendario, incluidos los fines de semana.
    • sin fechas duplicadas
  • La mesa del calendario puede ser marcado como “tabla de fecha” (especificando la columna de fecha cuando se le solicite).
    mark-as-date-table-3796289
    • Debe usar este paso si la tabla de calendario está unida a una tabla de datos usando una clave sustituta (por ejemplo, si su unión está en una columna de texto como DDMMYYYY, que técnicamente es texto, no una fecha).
    • Debe utilizar este paso si desea utilizar medidas rápidas que le ayuden a escribir funciones de inteligencia de tiempo utilizando su propia tabla de calendario.
    • Aparte de los 2 escenarios anteriores, este paso es no es obligatorio para que la mesa del calendario funcione.

Características adicionales de una buena tabla de calendario

Además de las características obligatorias anteriores (para que la inteligencia de tiempo incorporada funcione), hay algunas cosas que debe hacer para aprovechar al máximo cualquier tabla de calendario que cree.

  • Incluya columnas para cada atributo de hora que desee utilizar en sus informes. por ejemplo, año, mes, semana, día, nombre de la semana, etc.
  • Incluya una columna de ID de número entero que comience en 1 y se incremente en 1 para cada columna relevante del calendario. Por ejemplo, cree una columna MonthID que comience en 1 y se incremente en 1 para cada mes sin reiniciarse al final de cada año (1,2,3,… 12,13,14, etc.). Esto es muy útil para escribir fórmulas personalizadas de inteligencia de tiempo DAX.
  • Incluya una columna numérica para cada columna alfa que deba ordenarse en un orden específico. Esto se debe a que las columnas de Power BI siempre se ordenan en orden alfanumérico. Por lo tanto, la columna del mes se ordenará en abril, agosto, diciembre, febrero en lugar de enero, febrero, marzo y abril. Debe incluir una columna de mes numérica para poder decirle a Power BI que ordene la columna de mes alfa en un orden diferente. Utilice el botón “ordenar por columna” dentro de Power BI para cambiar el orden de clasificación predeterminado de la columna alfa. También tenga en cuenta que la columna de clasificación numérica debe tener una relación de 1 a 1 con la columna alfa. es decir, debe haber 1 y solo 1 valor en la columna numérica para cada valor en la columna alfa. (tenga en cuenta que esta es la forma más fácil de implementar, sin embargo, tenga en cuenta que técnicamente no tiene que ser 1 a 1. Lea sobre eso en el blog de Daniil Aquí )

sort-by-column-2021933

super-charge-power-bi-ad_1-1024x128-7785178

Cómo conectar tablas de datos a una tabla de calendario

Cargue su tabla de calendario en el modelo de datos de Power BI. Luego debe unir sus tablas de datos a la tabla de calendario usando la columna de fecha de ambas tablas. También es posible unir su tabla de datos a la tabla de calendario usando una columna de clave de fecha (clave sustituta) en lugar de una columna de fecha. Esto es más común cuando su tabla de calendario tiene un nivel de granularidad diferente al “día”, por ejemplo, semana, mes, etc.

A continuación, he unido una tabla de datos a la tabla de calendario usando las columnas de fecha en ambas.

date-table-relationship-5217979

Cómo crear una tabla de calendario

Hay varias formas de crear una tabla de calendario para Power BI.

  • Cárguelo desde un almacén de datos (si tiene uno)
  • Construye uno en Excel
  • Cree uno en Power Query
  • Usar funciones de tabla DAX

Tablas de calendario de Excel

Creo que Excel es el más flexible porque puede codificar cualquier valor que necesite. Esto puede ser bueno si tiene reglas comerciales variables en cuanto a cuándo comienza su año (por ejemplo, para 445 calendarios). Simplemente cree una hoja de trabajo en blanco y comience a agregar las columnas que necesita. Puede usar fórmulas en sus columnas para calcular los valores para Año, Mes, etc. Por ejemplo, puede usar = AÑO ([Date]) para crear una columna de año a partir de su columna de fecha, aunque esto generalmente solo funciona para calendarios regulares (no calendarios 4/4/5, etc.). Simplemente codifique los valores que no sigan ninguna lógica de fórmula.

image_thumb3-6677933

Importe los datos de su libro de Excel en cada libro de Power BI donde necesite una tabla de calendario.

Tablas de calendario de Power Query

Mi enfoque preferido para crear una tabla de calendario es usar Power Query. Una vez que escribe la consulta, se “configura y se olvida”. La tabla crecerá automáticamente a medida que pase el tiempo (no es algo que suceda con Excel). Tengo un artículo de blog separado sobre cómo hacer esto en Power Query.

https://exceleratorbi.com.au/build-reusable-calendar-table-power-query/

Si desea una lección completa sobre cómo usar Power Query, consulte los detalles del curso de capacitación haciendo clic en la imagen a continuación.

pq-data-trasfomration_banner-300x41-1460549

Funciones de la tabla DAX

Si bien es posible construir una tabla de calendario usando funciones DAX, no recomiendo este enfoque. Creo en el uso de Power Query para preparar los datos siempre que sea posible, por lo que esa es mi recomendación para la mayoría de los casos de uso. Puedes usar el Plantilla de tabla de calendario SQLBI si desea utilizar DAX. Pero tenga cuidado, el DAX tiene 1.500 líneas de código.

Cómo usar un calendario semanal o mensual

Hasta ahora solo he hablado de calendarios diarios. Si desea utilizar las funciones de inteligencia de tiempo incorporadas, debe utilizar un calendario diario como se describe anteriormente. Sin embargo, si sus datos se encuentran en un nivel de granularidad semanal o mensual y no desea / necesita usar las funciones de inteligencia de tiempo incorporadas, entonces puede usar un calendario semanal o mensual. La diferencia es que las funciones de inteligencia de tiempo integradas no funcionarán. Tenga en cuenta que aún puede crear sus propias fórmulas de inteligencia de tiempo personalizadas, pero el DAX generalmente es más difícil de escribir (DAX de nivel intermedio).

Por ejemplo, en lugar de esto

Total Sales FYTD =
     TOTALYTD([Total Sales], 'Calendar'[Date], "30/6")

Necesitarías escribir algo como esto

Total Sales FYTD =
     CALCULATE(
          [Total Sales],
          FILTER(ALL('Calendar'),
                 'Calendar'[FinYear] = MAX('Calendar'[FinYear]) &&
                 'Calendar'[FinWeek] <= MAX('Calendar'[FinWeek])
          )
     )

Tengo un artículo de blog completo sobre inteligencia del tiempo aquí https://exceleratorbi.com.au/dax-time-intelligence-beginners/

Además, en un calendario semanal o mensual, generalmente no usa una columna de fecha, sino que crea algún otro ID de sello de tiempo único para unirse a las tablas. por ejemplo, puede crear una columna como YYYYWW como clave (2101 para la Semana 1 de 2021, 2102 para la Semana 2 de 2021, etc.). Solo asegúrese de que se use la misma lógica en su tabla de calendario y también en su (s) tabla (s) de datos. Lo mismo se aplica si desea utilizar meses como granularidad de sus datos. Este es un fragmento de un calendario semanal típico que usaría la columna FinYearWeek como clave para unirse a la tabla de ventas.

weekly-calendar-table-8496017

Tenga en cuenta que es importante utilizar AAAAMM y no MMYYYY para una columna de clave. AAAAMM se ordenará naturalmente en orden cronológico, mientras que MMYYYY no lo hará.

error: Atención: Contenido protegido.