Archivo el 27/05/2020

Integración de varios archivos con la misma estructura desde una carpeta de sharepoint en dataflows.

Solución de problemas e introducción a la creación de funciones en Power Query

Siempre digo que soy un #dataflow_lover. Todos mis proyectos intento desarrollarlos mediante flujo de datos aunque en ocasiones las cosas se ponen complicadas debido a algunos errores que pueden aparecer. En las últimas semanas, la integración de carpetas con sharepoint está dando algún quebradero de cabeza. Vamos a analizar uno concreto, la integración de archivos con una misma estructura desde una carpeta de sharepoint.

Para empezar, en cuanto creamos un dataflow y elegimos Carpeta de Sharepoint como Origen de datos nos encontramos con el primer error. La secuencia es la siguiente:

Escogemos Carpeta de Sharepoint como origen de datos
Introducimos la URL (ya sabeis que tiene que ser el directorio Raíz, sin la carpeta Documentos) e iniciamos sesión con nuestra cuenta de Microsoft (al ser un origen Cloud no necesitamos gateway)

Y lo primero que nos encontramos es que la carpeta aparece vacía.

Esto es muy sencillo de resolver. El conector del Dataflow no está escribiendo correctamente la sentencia y basta con añadir al código lo siguiente:

SharePoint.Files("https://quolutions.sharepoint.com/sites/quolutions", [ApiVersion = 15])
Con [ApiVerion]=15 primer problema resuelto

Vamos a escoger sólo dos archivos de la carpeta para el ejemplo:

Le damos a las flechitas para combinar y nos devuelve un error

Antes de crear las funciones de combinación de manera automática como el desktop, el primer paso de filtrado de archivos ocultos devuelve un error.

Esto no pasa en el desktop que va a mostrar el cuadro de diálogo de combinación de archivos y va a crear las funciones necesarias después de aceptar:

¿Cómo podemos resolver esto en Dataflow? No he encontrado documentación por ningún sitio. Incluso hay un hilo en la comunidad sin resolver: https://community.powerbi.com/t5/Service/quot-Unexpected-error-quot-when-reading-sharepoint-folder-full/td-p/841277

Como yo lo resuelvo es simplemente creando yo la función de combinación. De hecho incluso en el desktop, no me gusta la forma en que Power Query realiza la automatización de la función y suelo crear yo mi propia función. El procedimiento es sencillo.

1º Escojo sólo un archivo con el que trabajar las transformaciones, hago doble click sobre binary en uno de ellos y me devuelve la información del csv en este caso. Valdría igual con excel o cualquier otro origen.

El código que me genera es el siguiente:

let
  Origen = SharePoint.Files("https://quolutions.sharepoint.com/sites/quolutions", [ApiVersion = 15]),
  Navegación = Origen{[Name = "LOG_20200513.csv", #"Folder Path" = "https://quolutions.sharepoint.com/sites/quolutions/Documentos compartidos/General/Argomaniz/DATOS_VISION/"]}[Content],
  #"CSV importado" = Csv.Document(Navegación, [Delimiter = ";", Columns = 17, QuoteStyle = QuoteStyle.None]),
  #"Encabezados promovidos" = Table.PromoteHeaders(#"CSV importado", [PromoteAllScalars = true]),
  #"Tipo de columna cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos", {{"FECHA", Int64.Type}, {"HORA", type text}, {"LADO", type text}, {"A1", Int64.Type}, {"A2", Int64.Type}, {"R1_A", Int64.Type}, {"R1_B", Int64.Type}, {"R2", Int64.Type}, {"R3", Int64.Type}, {"R4", Int64.Type}, {"R5", Int64.Type}, {"R6", Int64.Type}, {"B1", Int64.Type}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", Int64.Type}, {"RESULTADO", type text}})
in
  #"Tipo de columna cambiado"

Ahora lo que quiero es simplemente crear una función que tenga como parámetro el nombre del archivo. Para crear la función, tengo que envolver todo el código anterior en un let …. in en el que creo el parámetro y sustituir en el código anterior el nombre del archivo por el parámetro. El código de la función quedaría así:

let
  archivos = (nombrearchivo as text) =>  //este es el parámetro creado



let
  Origen = SharePoint.Files("https://quolutions.sharepoint.com/sites/quolutions", [ApiVersion = 15]),
  Navegación = Origen{[Name = nombrearchivo, #"Folder Path" = "https://quolutions.sharepoint.com/sites/quolutions/Documentos compartidos/General/Argomaniz/DATOS_VISION/"]}[Content], //aquí cambiamos el nombre del archivo por el parámetro
  #"CSV importado" = Csv.Document(Navegación, [Delimiter = ";", Columns = 17, QuoteStyle = QuoteStyle.None]),
  #"Encabezados promovidos" = Table.PromoteHeaders(#"CSV importado", [PromoteAllScalars = true]),
  #"Tipo de columna cambiado" = Table.TransformColumnTypes(#"Encabezados promovidos", {{"FECHA", Int64.Type}, {"HORA", type text}, {"LADO", type text}, {"A1", Int64.Type}, {"A2", Int64.Type}, {"R1_A", Int64.Type}, {"R1_B", Int64.Type}, {"R2", Int64.Type}, {"R3", Int64.Type}, {"R4", Int64.Type}, {"R5", Int64.Type}, {"R6", Int64.Type}, {"B1", Int64.Type}, {"B2", Int64.Type}, {"B3", Int64.Type}, {"B4", Int64.Type}, {"RESULTADO", type text}})
in
  #"Tipo de columna cambiado"
in
  archivos
Y ya tenemos nuestra función creada

No os lo había advertido pero lo mejor es crear la función sobre una copia de la consulta antes de hacer las transformaciones de la tabla porque ahora sobre ese archivo original es sobre el que invocaremos la función.

Vamos a quedarnos para este ejemplo con 3 logs unicamente

Nos quedamos sólo con la columna name e invocamos la función que previamente habíamos creado.

En este punto, el usuario si no tiene mucha experiencia puede entrar en pánico porque esto no existe en los dataflow

Pero a poco que conozcamos un poco como funciona M, la invocación de una función no es más que agregar una columna personalizada y eso si podemos hacerlo en los dataflow y en la fórmula pondremos:

Nombrefunción([ColumnaParametro])

Mi función se llama consulta y la columna que quiero invocar en el parámetro es el nombre del archivo (name)

Le damos a aceptar y ya nos trae cada una de las tablas con todas las transformaciones que hayamos hecho. En el ejemplo no he hecho ninguna transformación pero podríamos hacer las que quisiéramos. Sólo tenemos que expandir la tabla y listo.

Espero que sea de utilidad y que estas pequeñas piedras en el camino no os aparten de la senda de los Dataflow

Calendarios Personalizados (I). El Año Fiscal

En muchísimos contextos sobre todo financieros, el Año Fiscal no tiene por qué coincidir con el año natural. Ello conlleva que haya que realizar algunos cambios en nuestra Dimensión Fecha y que las funciones de inteligencia temporal no puedan usarse de igual modo que con el calendario normalizado. No vamos a entrar en este momento a tratar el tema de los cálculos de inteligencia temporal, que abordaremos después de que veamos todas las funciones de inteligencia temporal con los calendarios normalizados. En este post lo que voy a enseñar es como introducir las columnas propias del calendario fiscal en nuestra Dimensión Fecha.

En primer lugar estableceremos una nueva variable en nuestro calendario que denominaremos __PrimerMesFiscal, para poder dinamizar los cálculos en función de esta variable.

P.ej. var __PrimerMesFiscal=7 para un calendario fiscal que comienza el 1 de julio, en las Sociedades Anónimas Deportivas en España tienen este calendario fiscal.

Y una vez que tenemos esa variable, las columnas que crearemos en nuestra tabla de fechas serán las siguientes:

"NumAñoFiscal", IF(MONTH([Date])<___PrimerMesFiscal,YEAR([Date])-1,YEAR([Date])),
"NumMesFiscal", IF(MONTH([Date])<___PrimerMesFiscal,13-___PrimerMesFiscal+MONTH([Date]),MONTH([Date])-___PrimerMesFiscal+1),
"NumTrimestreFiscal", ROUNDUP(IF(MONTH([Date])<___PrimerMesFiscal,13-___PrimerMesFiscal+MONTH([Date]),MONTH([Date])-___PrimerMesFiscal+1)/3,0),
"AñoFiscal", "FY-"&IF(MONTH([Date])<___PrimerMesFiscal,YEAR([Date])-1,YEAR([Date])),
"MesFiscal", "FM-"&FORMAT(IF(MONTH([Date])<___PrimerMesFiscal,13-___PrimerMesFiscal+MONTH([Date]),MONTH([Date])-___PrimerMesFiscal+1),"0#"),
"TrimestreFiscal", "FQ-"&ROUNDUP(IF(MONTH([Date])<___PrimerMesFiscal,13-___PrimerMesFiscal+MONTH([Date]),MONTH([Date])-___PrimerMesFiscal+1)/3,0),
"EjercicioMesFiscal", "FYM-"&IF(MONTH([Date])<___PrimerMesFiscal,YEAR([Date])-1,YEAR([Date]))&"-"&FORMAT(IF(MONTH([Date])<___PrimerMesFiscal,13-___PrimerMesFiscal+MONTH([Date]),MONTH([Date])-___PrimerMesFiscal+1),"0#"),
"EjercicioTrimestreFiscal", "FYQ-"&IF(MONTH([Date])<___PrimerMesFiscal,YEAR([Date])-1,YEAR([Date]))&"-"&ROUNDUP(IF(MONTH([Date])<___PrimerMesFiscal,13-___PrimerMesFiscal+MONTH([Date]),MONTH([Date])-___PrimerMesFiscal+1)/3,0)

Como podemos observar, el cálculo es diferente si el mes de la fecha es menor que el primer mes fiscal o mayor y por eso lo construimos siempre con IF

Como he dicho, en este post no vamos a ver cómo utilizar la inteligencia temporal con un calendario fiscal, algo que veremos con más detenimiento más adelante, pero si voy a hablar de la excepción a la regla. En principio, cuando utilizamos un calendario fiscal no nos van a servir las funciones de inteligencia temporal, sino que tendremos que estudiar unos patrones de cálculos, sin embargo, hay una función de inteligencia temporal que si nos va a admitir la configuración del calendario fiscal. Se trata de la acumulación con TOTALYTD.

La función TOTALYTD o la anterior CALCULATE(Expresion,DATESYTD…) que devuelve la expresión de manera acumulada en el ejercicio, puede contener un parámetro denominado fin de año, para ajustar el calendario fiscal. Por ejemplo para las ventas acumuladas en un calendario fiscal cuyo mes de inicio es el mes de julio, la métrica sería:

Ventas YTD = TOTALYTD([Ventas],dimFechas[Fecha],"30-6") 

que sitúa el 30 de junio como fin del calendario fiscal.

En el próximo post sobre inteligencia temporal hablaremos del cálculo de los desvíos en nuestro calendario fiscal y agregaremos diferentes períodos relacionados con el calendario fiscal en nuestra dimensión período.

Adjuntamos dimFechas con los nuevos cálculos de calendario fiscal.

Y si quereis estar al tanto de las novedades del blog no olvidéis suscribiros

Simulando seguridad a nivel de página con Power BI

Con la actualización de Mayo una de las principales novedades ha sido la posibilidad de establecer un sistema de navegación mediante acciones condicionadas. Gracias a esto y con una sencilla técnica podemos realizar algo que hasta ahora era imposible, obtener seguridad a nivel de página, podemos establecer qué usuarios pueden acceder a qué páginas dentro de un informe.

En el siguiente vídeo, os lo muestro.

Dimensión período

La dimensión período es un conjunto de fechas agrupadas por períodos que permiten filtrar nuestros informes. En nuestro anterior post veíamos cómo elaborar el calendario de fechas en DAX y establecíamos una serie de columnas calculadas a las que llamábamos desvíos (offsets) y ya advertíamos que los desvíos nos servirían para establecer muchos cálculos dinámicos en función del día de hoy.

En este post, vamos a ver como elaborar la dimensión período, qué características tiene y como funciona en nuestros informes.

La primera vez que vi la utilización de la dimensión período fue en el blog de Chris Webb https://blog.crossjoin.co.uk/2016/05/30/creating-current-day-week-month-and-year-reports-in-power-bi-using-bidirectional-cross-filtering-and-m/ y me quedé maravillado con su potencial. Consistía en establecer una tabla dimensional de períodos temporales tales como el ejercicio actual, el trimestre actual, el mes actual, el día de ayer o el de hoy que podíamos usarlos como filtros en un informe, de manera que, con un solo cálculo de ventas y filtrado por esa dimensión, podíamos obtener el total de las ventas en el contexto temporal seleccionado y además, dinámicamente, según vaya cambiando el día de hoy, el período temporal va cambiando.

Chris Webb en ese post de referencia, explicaba cómo crear la dimensión período en lenguaje M. Yo en esta entrada voy a explicar cómo crearla en DAX. ¿Es mejor crearla en DAX o en M? Con la tabla de fechas pasa lo mismo, tenemos la opción de crearla en M o como expliqué en el anterior post, podemos crearla en DAX. No es mi propósito valorar cuál es la forma más adecuada de hacerlo, yo simplemente prefiero este tipo de tablas crearlas en DAX y por ello explico cómo hacerlo pero ambas formas son totalmente válidas. 

Nuestra tabla Período va a contener 3 campos: El campo Fecha, que incluirá todas las fechas que abarcan un período determinado, el campo NombrePeriodo, que será el nombre descriptivo del período y el Id, que podremos utilizar si queremos simplificar cálculos en los que utilicemos la dimensión período.

 Antes de comenzar a crear y a analizar nuestra tabla Periodo tendremos que crear una tabla auxiliar de fechas. Esto se debe a que si utilizamos la tabla de fechas directamente para crear la tabla de períodos, cuando posteriormente establezcamos la relación entre la tabla de fechas y la tabla de períodos se producirá una referencia circular. Eso lo salvamos creando una tabla auxiliar de fechas, que podremos ocultar ya que sólo nos servirá como base para la creación de la tabla período.

En el post anterior https://powerbisp.com/creacion-de-la-dimension-calendario-en-dax creábamos la tabla de fechas completa a partir de una variable de tabla realizada con el CALENDAR. Debemos coger el mismo rango de fechas que pusimos en esa variable para crear nuestra tabla de fechas auxiliar:

dFechasAux =  CALENDAR(DATE(2013,1,1);DATE(2021,12,31))

Una vez que tengamos nuestra tabla de Fechas Auxiliar, la relacionaremos con nuestra tabla de fechas en una relación que puede ser 1 a 1 en ambas direcciones, ya que en esencia, la tabla auxiliar contendrá las mismas fechas que la tabla de fechas. Ocultamos la Tabla de Fechas auxiliar y ya podemos empezar a construir nuestra dimensión Periodo.

Para la creación de nuestra tabla de Periodos utilizaremos tres fórmulas fundamentalmente:

UNION ya que vamos a ir creando cada periodo como una tabla independiente que uniremos con esa función.

SELECTCOLUMNS para seleccionar las tres columnas que queremos construir (Fecha, NombrePeriodo e Id)

CALCULATETABLE para seleccionar las fechas filtradas que engloban cada período y que nos servirá como la referencia de tabla del SELECTCOLUMNS.

Veámoslo paso a paso.

Si queremos construir el período Ejercicio Actual, en nuestra tabla de fechas teníamos un campo que denominábamos [desvio ejercicio]. Si el [desvio ejercicio]=0 estamos filtrando el ejercicio actual. Por tanto, para construir la tabla periodo del ejercicio actual la fórmula sería:

SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=0);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio Actual”;”Id”;1)

En esta fórmula le estamos diciendo que nos devuelva todas las filas del campo Fecha de la tabla filtrada dFechasAux donde el Desvio Ejercicio =0; una segunda columna “NombrePeriodo” que siempre será “Ejercicio Actual” y una tercera columna “Id” que siempre será 1

Para el Ejercicio Anterior o E-1 simplemente debemos cambiar el valor del “Desvío Ejercicio” por -1

SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=-1);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio E-1″;”Id”;2)

 Con el UNION unimos ambas tablas

UNION(SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=0);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio Actual”;”Id”;1); SELECTCOLUMNS(CALCULATETABLE(dFechasAux;dFechas[Desvio Ejercicio]=-1);”Fecha”;dFechasAux[Date];”NombrePeriodo”;”Ejercicio E-1″;”Id”;2))

De esta forma y utilizando los diferentes desvíos como filtro de la tabla de fechas, podemos hacer tantos períodos como podamos imaginar. He aquí un ejemplo de tabla más o menos completa de períodos:

dPeriodo =
UNION (
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio Actual",
        "Id", 1
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio E-1",
        "Id", 2
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Ejercicio] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio E-2",
        "Id", 3
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Ejercicio] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ejercicio Actual hasta hoy",
        "Id", 4
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Actual",
        "Id", 5
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Trimestre] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Actual hasta hoy",
        "Id", 6
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-1",
        "Id", 7
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-2",
        "Id", 8
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Trimestre] = -3 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Trimestre Q-3",
        "Id", 9
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes Actual",
        "Id", 10
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Mes] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes Actual hasta hoy",
        "Id", 11
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes M-1",
        "Id", 12
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Mes] = -2 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Mes M-2",
        "Id", 13
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Semestre] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Semestre Actual",
        "Id", 14
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            dFechas[Desvio Semestre] = 0,
            dFechas[HastaFecha] = "PASADO"
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Semestre Actual hasta hoy",
        "Id", 15
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -364, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 365 Dias",
        "Id", 16
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -29, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 30 Dias",
        "Id", 17
    ),
    SELECTCOLUMNS (
        CALCULATETABLE (
            dFechasAux,
            AND ( dFechas[Desvio Dia] >= -6, dFechas[Desvio Dia] <= 0 )
        ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ultimos 7 Dias",
        "Id", 18
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Dia] = 0 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Hoy",
        "Id", 19
    ),
    SELECTCOLUMNS (
        CALCULATETABLE ( dFechasAux, dFechas[Desvio Dia] = -1 ),
        "Fecha", dFechasAux[Date],
        "NombrePeriodo", "Ayer",
        "Id", 20
    )
)
**Formateado con daxformatter.com y apto para pegar directamente en 
Power BI con el sistema de DAX angloamericano.

*** Ante el cambio de política de Microsoft PowerBI con la idea de unificar el código DAX y la recomendación de utilizar el sistema angloamericano(update mayo-2020), a partir de este post intentaré utilizar siempre el sistema angloamericano, con (,) como separador, aunque me pese ya que siempre he sido de la opinión de conservar el sistema europeo. En las explicaciones el código puede estar con «;» (la fuerza de la costumbre) y por temas de wordpress las «(comillas) no son compatibles por lo que si vais a copiar directamente en Power BI utilizad sólo el código que marque para ello o los ficheros de descarga que adjuntaré.

La dimensión periodo se relaciona con nuestra tabla de Fechas Aux con una relación de varios a 1 pero en ambas direcciones.

De esta manera, las relaciones entre las diferentes tablas de fecha y calendario quedaría con el siguiente esquema:

Por último es necesario realizar una puntualización en cuanto a los cálculos del modelo ya que cualquier cálculo temporal que queramos realizar, deberá contener el filtro ALL(dPeriodo)

Por ejemplo el cálculo normal de Ventas E-1 (Ventas del ejercicio anterior) sin dimensión periodo sería:

Ventas E-1 = CALCULATE(SUM(Sales[SalesAmount]),DATEADD(dFechas[Fecha],-1,YEAR))

 Con la dimensión período, si queremos que nos funcione cuando filtramos por cualquier período quedaría así:

 Ventas E-1 = CALCULATE(SUM(Sales[SalesAmount]),ALL(dPeriodo),DATEADD(dFechas[Fecha],-1,YEAR))

NUEVAS CONSIDERACIONES SOBRE LA DIMENSÍON PERÍODO

Este post lo escribí por primera vez en abril de 2018 y desde entonces he utilizado siempre la Dimensión período creada en DAX en mis modelos, sin embargo en los últimos tiempos me he decantado más por utilizar los desvíos de mi calendario directamente en las medidas o en los slicer para filtrar dinámicamente el tiempo, ambas técnicas están más que probadas y ya es cuestión de la utilización que queramos darle.

Si es cierto que esta técnica en cuanto a performance no es de las más optimas. No he tenido problemas en datasets grandes con ella, pero pueden tenerse.

En octubre de 2019 Phil Seamark hablaba sobre ello en un post: https://dax.tips/2019/10/09/row-based-time-intelligence/ basándose en la dimensión período creada en M que no necesita de la tabla auxiliar y planteando el siguiente esquema de relación como más óptimo:

Esquema propuesto por Phil Seamark en su artículo. Time Intelligence equivaldría a mi dimensión período, pero en esta caso creada en M

Ciertamente he de confesar que no he probado esta técnica propuesta por Phil en mi modelo con la Dimensión período en DAX, pero prometo en cuanto pueda hacerlo y ofrecer mis propias conclusiones. No obstante he de decir que el artículo de Phil es muy recomendable y que explica en gran medida los beneficios (y las desventajas) de una dimensión como ésta y me enorgullece que sea una práctica habitual en mis modelos desde hace muchísimo tiempo y aceptadas por la comunidad de Power BI aunque no conocidas por todo el mundo.

*Os recuerdo que el DAX Angloamericano es con la separación con «,» y europeo con «;» y que desde la versión de mayo de Power BI se puede elegir entre uno u otro.

Creación de la dimensión Calendario en DAX

Uno de los grandes “poderes” de los modelos analíticos en Power BI es la inteligencia temporal en los cálculos. Ello nos permite que calcular medidas como las ventas y compararlas con períodos de ventas anteriores resulte extremadamente sencillo y potente, pero la base fundamental para ello es disponer de una buena y completa tabla de calendario con todas las columnas calculadas que puedan servirnos para nuestros análisis.

Las tablas de calendario pueden hacerse de diversas formas, desde el editor de Power Query en M o desde el propio entorno de Power BI Desktop con el lenguaje DAX.

Después de usar ambos sistemas y ver las bondades de uno y otro, me quedo con la programación de la tabla de Calendario en DAX y he elaborado una tabla de fechas con todas las columnas calculadas que utilizo en mi día a día en un sólo cálculo de tabla.

A partir de la función de tabla CALENDAR y usándola como variable, puede en un mismo cálculo insertarse cuantas columnas calculadas necesitéis con la función SELECTCOLUMNS.

La tabla definitiva de calendario que he elaborada obedece a la siguiente fórmula, que puede copiarse tal cual en la barra de fórmulas tras presionar “nueva tabla”:

DimFechas =
VAR Fecha =
    CALENDAR ( DATE ( 201911 )DATE ( YEAR ( TODAY () ) + 11231 ) )
RETURN
    SELECTCOLUMNS (
        Fecha;
        «Fecha»; [Date];
        «IdFecha»YEAR ( [Date] ) * 10000
            MONTH ( [Date] ) * 100
            DAY ( [Date] );
        «IdFechaEntero»INT ( [Date] );
        «Ejercicio»YEAR ( [Date] );
        «Mes»UPPER ( FORMAT ( [Date]; «MMMM» ) );
        «MesNro»INT ( FORMAT ( [Date]; «M» ) );
        «NroDia»INT ( FORMAT ( [Date]; «d» ) );
        «Trimestre»«T»
            ROUNDUP ( MONTH ( [Date] ) / 30 );
        «NroTrimestre»ROUNDUP ( MONTH ( [Date] ) / 30 );
        «DiaSemana»WEEKDAY ( [Date]; 2 );
        «Semana»WEEKNUM ( [Date]; 2 );
        «Nombre Dia»UPPER ( FORMAT ( [Date]; «DDDD» ) );
        «EjercicioTrimestre»COMBINEVALUES (
            «-«;
            YEAR ( [Date] );
            «T»
                ROUNDUP ( MONTH ( [Date] ) / 30 )
        );
        «NºSemana ISO»ROUNDDOWN (
            (
                [Date]
                    – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                    WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
            ) / 7;
            0
        );
        «MesCorto»UPPER ( FORMAT ( [Date]; «MMM» ) );
        «DiaEjercicio»UPPER ( FORMAT ( [Date]; «Y» ) );
        «EjercicioActual»YEAR ( TODAY () );
        «Mes Actual»MONTH ( TODAY () );
        «HoyEntero»INT ( TODAY () );
        «Trimestre Actual»ROUNDUP ( MONTH ( TODAY () ) / 30 );
        «Semestre»ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 );
        «EjercicioMes»COMBINEVALUES ( «-«YEAR ( [Date] )FORMAT ( [Date]; «MM» ) );
        «EjercicioISO»IF (
            OR (
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 53;
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 52
            )
                && MONTH ( [Date] ) = 1;
            YEAR ( [Date] ) – 1;
            IF (
                (
                    ROUNDDOWN (
                        (
                            [Date]
                                – DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 )
                                WEEKDAY ( DATE ( YEAR ( [Date] – WEEKDAY ( [Date] – 1 ) + 4 )13 ) ) + 5
                        ) / 7;
                        0
                    )
                ) = 1
                    && MONTH ( [Date] ) = 12;
                YEAR ( [Date] ) + 1;
                YEAR ( [Date] )
            )
        );
        «Desvio Ejercicio»YEAR ( [Date] ) – YEAR ( TODAY () );
        «Desvio Mes»IF (
            ( YEAR ( [Date] ) – YEAR ( TODAY () ) ) = 0;
            INT ( FORMAT ( [Date]; «M» ) ) – INT ( FORMAT ( TODAY ()«M» ) );
            INT ( FORMAT ( [Date]; «M» ) ) – INT ( FORMAT ( TODAY ()«M» ) )
                + (
                    12
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        );
        «Desvio Dia»INT ( [Date] ) – INT ( TODAY () );
        «HastaFecha»IF ( ( INT ( [Date] ) – INT ( TODAY () ) ) <= 0«PASADO»«FUTURO» );
        «Desvio Trimestre»IF (
            ( YEAR ( [Date] ) – YEAR ( TODAY () ) ) = 0;
            ( ROUNDUP ( MONTH ( [Date] ) / 30 ) )
                 ( ROUNDUP ( MONTH ( TODAY () ) / 30 ) );
            ( ROUNDUP ( MONTH ( [Date] ) / 30 ) )
                 ( ROUNDUP ( MONTH ( TODAY () ) / 30 ) )
                + (
                    4
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        );
        «Semestre Actual»ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 );
        «Desvio Semestre»IF (
            YEAR ( [Date] ) – YEAR ( TODAY () ) = 0;
            (
                ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 )
            )
                 (
                    ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 )
                );
            (
                ROUNDUP ( INT ( FORMAT ( [Date]; «M» ) ) * 2 / 120 )
            )
                 (
                    ROUNDUP ( MONTH ( TODAY () ) * 2 / 120 )
                )
                + (
                    2
                        * ( YEAR ( [Date] ) – YEAR ( TODAY () ) )
                )
        )
    )

El código dax ha sido formateado en https://www.daxformatter.com/ herramienta que os recomiendo para mejor legibilidad del código y os lo podéis descargar en txt en el siguiente enlace:

Además de diferentes columnas muy interesantes de fechas como la Semana ISO o el Ejercicio ISO para calendarios gregorianos, muy usado en nuestras empresas agrícolas, establezco los denominados desvíos (offsests) que introdujo Avi Singh, gurú del power BI americano, que permiten realizar infinidad de cálculos dinámicos en función del día de hoy.

Las fechas de inicio y fin pueden cambiarse fácilmente modificando sólo la tabla Fecha que se define en la variable. A modo de ejemplo he puesto desde el 1/1/2019 al 31/12/2021.

Espero que os sea de utilidad y si tenéis alguna sugerencia de columna que pondríais en la tabla de fechas podéis decírnoslo en los comentarios.

Configurar mediante un tema personalizado los encabezados de objetos visuales

De vez en cuando me da por trastear las entrañas de power bi para descubrir cosas nuevas. Hoy me planteaban una duda, ¿de qué manera se pueden configurar los encabezados de objeto visual para que no aparezca ningún icono pero no se impida al usuario exportar los datos a excel, por ejemplo.

En cualquier objeto visual, existe la opción de Activar o desactivar el encabezado del objeto visual

Y una vez activa, se pueden habilitar unos u otros encabezados según convenga:

Puedo desactivar todos y dejar activo el Icono Más opciones.

Pero si quiero automatizar este proceso, en todos los objetos visuales es tremendamente laborioso.

Lo primero que pensé fue en el generador de temas de powerbitips.com pero ellos contemplan la posibilidad de activar o desactivar el Encabezado completo, pero no unos iconos sí y otros no.

Busqué a ver si encontraba la solución por algún lado y no la encontré, hasta que recordé como descubrí los nombres de los iconos cuando salió el formato condicional y que Matt Allington mencionó en su blog:

https://exceleratorbi.com.au/power-bi-icon-names/

Seguro que en mi querido layout estaba la solución. Puse mis encabezados como activos, desactivé todos los iconos y comencé a bucear en el layout del pbix con el que estaba trabajando para descubrir como generar el código y efectivamente, logré que funcionara.

Personalizando el visual header desde power bi tips conseguí el siguiente código:

{"name":"theme created by powerbi.tips https://themes.powerbi.tips",
"visualStyles":
{"": {"":{"visualHeader":[{"show":true]}}}}
}

Sólo me quedaba descubrir cómo desactivar unos u otros iconos a mi antojo y en el layout estaban sus nombres.

{"name":"personalizado","dataColors":["#c1a131","#374649","#FD625E","#F2C80F","#5F6B6D","#8AD4EB","#FE9666","#A66999",
"#3599B8","#DFBFBF","#4AC5BB","#5F6B6D","#FB8281","#F4D25A","#7F898A","#A4DDEE","#FDAB89","#B687AC","#28738A","#A78F8F",
"#168980","#293537","#BB4A4A","#B59525","#475052","#6A9FB0","#BD7150","#7B4F71","#1B4D5C","#706060","#0F5C55","#1C2325"],
"visualStyles":
{"*":{"*":
{"visualHeader":[{"show":true,
"showVisualInformationButton":false,
"showVisualWarningButton":false,
"showVisualErrorButton":false,
"showDrillRoleSelector":false,
"showDrillUpButton":false,
"showDrillToggleButton":false,
"showDrillDownLevelButton":false,
"showDrillDownExpandButton":false,
"showPinButton":false,
"showFocusModeButton":false,
"showSeeDataLayoutToggleButton":false,
"showFilterRestatementButton":false,
"showOptionsMenu":true}]}}}}

Con este código, poniendo true o false según nos interese, podemos ocultar o mostrar programáticamente vía tema personalizado los encabezados de los objetos visuales, actuando sobre todos por defecto de esa manera o, si solo queremos que actúe en un objeto visual concreto, simplemente debemos sustituir el segundo asterisco por el nombre que la visual tiene en inglés (p. ej. Pie Chart)

El tema aparece con extensión .pdf pero es un .json, sólo hay que cambiarle la extensión y funciona.