¿Cuáles son las posibilidades de crear tablas de fechas en entornos de autoservicio?

Un ejemplo de esto es calcular el número de semanas de los años fiscales cuando no se alinean con los años calendario.

Hacer esto con una fórmula matemática es una pesadilla, o mis habilidades matemáticas no son lo suficientemente sofisticadas.

Consulta de energía y flujos de datos

Ahora llegamos a la última variante: usar Power Query o flujos de datos.

Para empezar, no distingo entre Power Query y Data Flows en v1 o v2, ya que todos operan según los mismos principios y usan el mismo lenguaje.

Empiezo a crear la tabla de fechas en Power Query creando tres parámetros:

StartYear: El primer año en la tabla de fechas YearsToLoad: Cuántos años debe cubrir la tabla de fechas FirstMonthOfFiscalYear: Que es el primer mes del año fiscal.
Si el año fiscal coincide con el año calendario, este será 1; en caso contrario, será el número del primer mes del Ejercicio Fiscal.

Todo el código adicional dependerá de estos parámetros.

El inicio es siempre con el mismo comando: List.Dates()

Los parámetros de esta función son:

La fecha de inicio El número de días para crear la lista El intervalo, que en este contexto es días

Esto lleva a una línea como esta, mientras se utilizan los parámetros mencionados anteriormente:

Lista.Fechas(#fecha(AñoInicio,1,1),366 * AñosParaCargar,#duración(1,0,0,0))

Y aquí viene el primer obstáculo:

Por lo general, necesitamos una tabla de fechas que abarque varios años. Pero cada cuatro años es bisiesto.

Entonces, ¿cómo podemos hacer esto, ya que Microsoft requiere una tabla de fechas que abarque años enteros?

La solución es obtener la última fecha del último año (31 de diciembre) y filtrar las filas para mantener solo aquellas anteriores o iguales a esta fecha.

Y esta es la razón por la que multiplico 366 días por el parámetro YearsToLoad.

Aquí está el código M completo para este escenario:

let Source = List.Dates(#date(StartYear,1,1),366 * YearsToLoad,#duration(1,0,0,0)), #"Convertido a tabla" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Columnas renombradas" = Table.RenameColumns(#"Convertida a tabla",{{"Columna1", "Fecha"}}), #"Tipo cambiado" = Table.TransformColumnTypes(#"Columnas renombradas",{{"Fecha", escriba fecha}}), #"Última fecha válida agregada" = Table.AddColumn(#"Tipo cambiado", "Última fecha válida", cada #fecha(Fecha.Año(List.Max(#"Tipo cambiado"[Fecha])) – 1, 12, 31), escriba fecha), #"Mantener solo fechas válidas" = Table.SelectRows(#"Última fecha válida agregada", cada [Fecha] <= [Última fecha válida]) en #"Mantener solo fechas válidas"

A continuación, puedo comenzar a agregar todas las columnas necesarias para crear una tabla de fechas completa.

Primero, agrego un Date_ID, con una representación numérica de la fecha:

Fecha.Año([Fecha]) * 10000 ) + (Fecha.Mes([Fecha]) * 100) + Fecha.Día([Fecha])

Esta columna debe establecerse en un tipo de datos entero. Por lo tanto, toda la línea de M-Code es esta:

Table.AddColumn(#"Mantener sólo fechas válidas", "Date_ID", cada uno ( Date.Year([Date]) * 10000 ) + (Date.Month([Date]) * 100) + Date.Day([Date]), Int64.Type)

Tenga en cuenta la expresión Int64.Type antes del último corchete de cierre. Esto establece el tipo de datos en el mismo comando, eliminando la necesidad de un paso adicional.

A continuación, puedo usar las posibilidades disponibles en el Editor de Power Query para agregar columnas adicionales que comúnmente agrego a mis tablas de fechas:

Como puede ver, podemos agregar una gran cantidad de columnas sin escribir código.

Pero en algún momento debemos escribir nuestro propio código para agregar columnas adicionales; por ejemplo, columnas para almacenar el año y el período correspondiente.

Estas son algunas de estas columnas:

Año/Mes Nombre Año/Trimestre Año/Semana

Luego, para las columnas de la fecha de inicio y finalización de cualquier período, como una semana o un mes.

Utilizo estas columnas para código de inteligencia de tiempo personalizado en DAX. Escribí algunos otros artículos aquí sobre este tema, como cálculos semanales.

Y en algún momento, el código M habitual resulta insuficiente para obtener la información requerida.

Por ejemplo, cuando necesito obtener una columna de año alineada con la semana (YearForWeek).

Para estos escenarios, comencé a escribir funciones M personalizadas que me permiten acceder a un rango de fechas para cada fila, lo que de otro modo sería imposible en M.

En este caso, agregué esta función:

(DateInput como fecha) como número => let ClosestThursday = Date.AddDays(DateInput, -1 * Date.DayOfWeek(DateInput, Day.Monday) + 3), Año = Date.Year(ClosestThursday) en Año

Si no está familiarizado con las funciones M personalizadas, le recomiendo encarecidamente que consulte esta excelente función.

Agregaré algunos enlaces en la sección Referencias a continuación.

Después de todo el desarrollo de la tabla de fechas, obtuve estas funciones personalizadas:

ObtenerISOAño
Obtenga el año alineado semanalmente GetISOWeek
Calcule el número de semana correcto según el estándar ISO CalculateMonthDiff
La diferencia en meses entre dos fechas CalculateQuarterDiff
La diferencia en trimestres entre dos fechas GetFiscalWeekNumber
Calcule el número de semana comenzando con la semana del día en que comienza el año fiscal. Obtener año fiscal actual
Esto obtiene el año fiscal actual según la fecha actual. Obtener año de inicio fiscal actual
Esto calcula el año en el que comienza el año fiscal actual.

Me llevó algo de tiempo (entre 2 y 3 días de trabajo), pero logré integrar todas las columnas en la tabla de fechas, lo que considero útil en la mayoría de los escenarios.

Pero los conceptos básicos del lenguaje M agregaron trabajo y complejidad adicionales, que no son necesarios, por ejemplo, en SQL.

Pero en lugar de copiar todo el código M aquí, le daré acceso al archivo Power BI que contiene la solución completa con la tabla de fechas.

¿Qué sigue?

Bueno, ahora puedes tomar el M-Code completo, copiarlo en un flujo de datos y compartirlo en toda tu organización.

Para permitir el acceso a su flujo de datos, es suficiente con otorgar permisos de espectador a los consumidores en el espacio de trabajo.

De esta manera, tendrá una única versión centralizada de la tabla de fechas que todos pueden usar.

Este es el punto principal que hace que este enfoque sea muy útil.

Es lo mismo que cuando tienes una plataforma de datos centralizada, donde construyes una tabla de fechas. Pero como no todo el mundo tiene esto, utilizar un flujo de datos es un buen punto medio.

En mi trabajo con Data Flows, descubrí que solucionar problemas de una importación fallida puede resultar engorroso. He descubierto que los mensajes de error pueden ser mínimos y es posible que se pierdan detalles importantes.

¿Cuál usar?

¿Qué recomiendo usar?

Primero, cuando tenga un almacén de datos centralizado, ya sea local o basado en la nube o si es una base de datos relacional u otro almacén de datos, utilícelo para crear su tabla de fechas.

Como ya mencioné, no hay duda al respecto.

En un escenario de BI de autoservicio, o cuando la empresa no es tan grande, la decisión no es tan sencilla.

En primer lugar, depende de las habilidades disponibles.

Después de crear la tabla de fechas en Power Query, descubrí que es mucho más fácil crear una tabla de fechas en DAX que en Power Query.

Las capacidades de DAX hacen que sea más fácil crear una tabla de fechas que con M-Code en Power Query.

Puedo definir la tabla en una única declaración DAX y agregar lógica compleja en columnas calculadas adicionales.

Pero cada tabla de fechas DAX es local para cada modelo semántico de Power BI. Por lo tanto, terminará con varias tablas de fechas que pueden diferir entre sí.

Pero tan pronto como tenga varios equipos creando soluciones Power BI, puede resultar beneficioso crear una única tabla de fechas central en un único espacio de trabajo y compartirla con todos los equipos.

Cuando alguien necesita una nueva función en la tabla de fechas, se agregará a la tabla central y todos podrán beneficiarse de ella.

Por supuesto, esto es válido para cualquier variante de tablas de fechas centralizadas.

En tales casos, el desarrollador del modelo de datos siempre puede decidir qué columnas importar, evitando la importación de columnas innecesarias al modelo de datos.

Conclusión

Ahora conoces las diferentes formas de crear una tabla de fechas.

Tú decides entre las posibilidades disponibles.

Pero será difícil cambiar de una tabla DAX local a cualquier tabla centralizada.

Debe considerar lo antes posible qué camino tomará para evitar el trabajo adicional de cambiar entre ellos.

Tómate tu tiempo y habla con todos los miembros del equipo o creadores de modelos potenciales para elegir la forma correcta.

No tiene sentido decidir construir una tabla de fechas centralizada cuando nadie la usa.

Por lo tanto, asegúrese de que todos estén de acuerdo con el uso de la tabla de fechas central.

Referencias

Aquí, la documentación de Microsoft sobre funciones personalizadas en M:

https://learn.microsoft.com/en-us/powerquery-m/m-spec-functions

Una página en Microsoft Obtenga información sobre funciones personalizadas:

https://learn.microsoft.com/en-us/power-query/custom-function

Una buena explicación de Wicked Smart Data:

https://www.wickedsmartdata.com/articles/custom-m-functions-power-query

Si prefieres ver un vídeo para aprender, este explica las funciones personalizadas desde cero:

Este vídeo plantea la pregunta de cuándo son útiles las funciones personalizadas:

Esto le muestra cómo resolver un desafío, incluido un enfoque muy práctico sobre cómo desarrollar fácilmente una función personalizada: