DAX: Funciones de inteligencia de tiempo

Una de las grandes ventajas de Power Bi es que tiene funciones para comparar los datos por periodos de tiempo. Por ejemplo: ventas entre años, lo que llevamos vendido del año, la Tendencia Anual Móvil, etc…

Pero para que esto funcione es necesario disponer de un intervalo continuo de fechas, y lo más normal es que en nuestras tablas de datos no haya ventas todos los días.

Así que aprenderemos como crear una tabla de fechas, un calendario, como relacionarlo con nuestras ventas y como sacar partido de las funciones de inteligencia de tiempo

Tabla Calendario

Vamos a datos, modelado, e indicamos nueva tabla

Dato que tenemos ventas desde el 2013 hasta el 2015, ponemos

Calendario = CALENDAR(“1-1-2013″;”31-12-2015”)

Y esto nos crea una nueva tabla que tiene todas las fechas en ese intervalo

Pero en realidad nuestras ventas son dinámicas, y hoy tenemos estos periodos y mañana podemos tener otros, así que estaría bien tener una tabla dinámica, que coja desde la primera fecha de la que hay ventas, hasta la última fecha en la que hay ventas.

Así que cambiemos la definición de la tabla y pongamos

Calendario = CALENDAR(MIN(‘Facturas Ventas'[Fecha]); MAX(‘Facturas Ventas'[Fecha]))

O sea desde el día mínimo hasta el día máximo

Ahora vemos como el calendario empieza el 5-1-13, el primer día que hay ventas

Y termina el 22-12-15

Pero en realidad lo que nos interesa es desde el 1-1 del primer año que hay ventas, hasta el 31-12 del último año que hay ventas, para así poder comparar periodos completos, años, trimestres, etc…

Para ello aprovecharemos la función DATE que permite indicar un año, un mes y un día, y a partir de esos tres parámetros construye una fecha

Sustituyamos pues nuestra definición de la tabla por

Calendario = CALENDAR(DATE(YEAR(MIN(‘Facturas Ventas'[Fecha]));1;1); DATE(YEAR(MAX(‘Facturas Ventas'[Fecha]));12;31))

Ahora sí, tenemos del 1-1-13 al 31-12-15

Y lo veremos más claro si le damos formato fecha con dd-mm-yyyy

Nos queda un último paso importante, vincular la fecha de nuestras ventas con este calendario. Para ello vamos a Relaciones

Y arrastramos la Fecha de Facturas ventas a la Date de Calendario. Power Bi establece una relación y nos la muestra.

Esta es una de las grandes ventajas de Power BI, podemos relacionar datos de distintas tablas y luego usarlas en nuestros informes.

Para empezar a aprovechar las funciones de inteligencia de tiempo, vamos a crear una medida que nos permita comprar las ventas de un periodo con las ventas del mismo período del año anterior.

Volvemos a Datos, seleccionamos la tabla facturas ventas, modelado y creamos una nueva medida

Importe Anterior = CALCULATE([Importe];SAMEPERIODLASTYEAR(Calendario[Date]))

Y le ponemos formato con separador de miles y dos decimales.

Hemos usado la función CALCULATE, que tiene varios parámetros. El primero es una expresión, en este caso hemos usado una medida que ya tenemos definida, el Importe. Igualmente hubiésemos podido poner SUM(NETO), pero esa es la ventaja de definir la medida, nos olvidamos de arrastrar su fórmula. Y los siguientes parámetros son filtros, en este caso usamos la función SAMEPERIODLASTYEAR donde le indicamos que busque la fecha en el Calendario, NO en Facturas ventas, dado que necesita un intervalo continuo de fechas.

Para verificarlo, creamos una nueva página, ponemos una tabla con los campos Date, del Calendario, e Importe e Importe anterior de facturas ventas.

Veremos que al poner date se nos desglosa en Año, trimestre, mes y día. Quitemos el mes y el día, quedando

Si observamos bien la tabla veremos que a partir del 2014, en la columna importe anterior aparecen las ventas del mismo trimestre del año anterior

Vemos que en el tercer trimestre de 2015 no hay ventas, pero si nos muestra a su derecha las el tercer trimestre de 2014

Por supuesto si activamos los meses, pasara lo mismo.

Y si queremos saber el incremento de un periodo respecto a otro, basta con crear una medida que relacione las dos que ya tenemos

% Importe s/Anterior = [Importe]/[Importe Anterior]

A la que podemos poner formato porcentaje, y añadirla a nuestra tabla

Obteniendo así el aumento de un trimestre respecto a al mismo del año anterior.

Otra función muy útil es la YearToDate, que nos permite saber valores acumulados hasta una fecha

Creemos una nueva medida y ponemos

Importe Acumulado = TOTALYTD([Importe];Calendario[Date])

Con formato separador de miles y dos decimales, y la añadimos a la tabla

Vemos que el 2013 empieza a sumar, y cada trimestre es la suma de sí mismo con el anterior. Y que al cambiar de año, empieza a sumar de nuevo.

Le propongo crear una tabla que nos muestre los meses, y tres columnas con los años que tenemos datos, y los acumulados de los meses, así cuando miremos un mes, no vemos sus ventas, sino lo que llevamos de acumulado y es más fácil saber si vamos bien o mal, pues eso compensa si un mes hay más o menos ventas que los anteriores.

En la misma página podemos hacer más pequeña la fuente, y más estrecha la tabla para dejar espacio debajo

Y añadimos una Matriz, con en Valores, el importe acumulado, en filas el mes, en columnas el año

Y obtenemos una tabla con las ventas acumuladas mes a mes.

Guardemos la pagina como Inteligencia de tiempo

20 comentarios sobre “DAX: Funciones de inteligencia de tiempo”

  1. Felicidades por tu magnífico trabajo.
    Tengo una duda, tú lo haces con la tabla ventas, pero imagina que tienes tablas de albaranes, presupuestos, pedidos, etc. Ya que existen relaciones de estas tablas con la de ventas no puedes usar la misma tabla calendario porque da errores ¿haces una tabla calendario por cada tabla de cabecera de esos documentos?

    1. No acabo de entender esto de las tablas relacionadas entre si.
      Yo tengo pedidos por un lado, albaranes por otro y facturas por otro, y relaciono las fechas de cada tabla con la de calendario.

      Si quieres mandame un mail y te envio un pantallazo de como lo tengo yo

  2. En la estructura normal que relaciona Pedidos con albaranes y facturas con pedidos, si creo la tabla calendario basada en las fechas de las facturas, funciona perfecto para estas, pero si también quiero montar una relación de pedidos con calendario, el Powerbi me dice que ya existe una relación entre facturas y pedidos y no me deja.
    Si desactivo la relación entre ellas, por supuesto puedo montar la relación entre cada una y el calendario.
    Te agracecería el pantallazo

    1. Tienes razon, lo he probado relacionando los pedidos con los albaranes
      Luego creado el calendario y relacionandolo con el pedio
      Y cuando intento crear la relacion albaran – calendario, NO lo permite
      Cuando pueda investigare por la web

  3. Te comento que he seguido investigando y algo he conseguido. Puedes crear una segunda tabla calendario_P para por ejemplo los pedidos, y creas solo el campo Date y lo relacionas con la tabla calendario completa. De esta forma sí permite trabajar con datos indistintos de todas las tablas.

  4. Hola, Primero te quiero agradecer por el Blog es muy bueno.
    Estoy tratando de hacer lo de SAMEPERIODLASTYEAR(Calendario[Date]) y todo los valores que corresponden al año anterior me los coloca como si fuera 2017. No se porque sucede al igual que el acomulado.

  5. Estimado,
    Estoy aprendiendo POWER BI, soy empleado publico y trabajo con presupuesto que se ejecuta todos los meses. Estoy trabando de hacer una formula que me calcule el porcentaje de ejecucion de presupuesto sobre el total de presupuesto que se ejecuta en un año.
    Un ejemplo de mi tabla es la siguiente. Me ha sido imposible poder generar con formulas las ultimas dos columnas.

    Ojala puedan ayudarme. De ante mano muchisimas gracias.

    MES–EJECUTADO–AVANCETOTAL–AVANCE %
    01——10—————–10—————–8,6%
    02——-0——————10—————–8,6%
    03——20—————–30—————-26,0%
    04——35—————–65—————-56,5%
    05——50—————–115—————100%

  6. Hola! gracias por el post! pero tengo un problema ya que al crear el importe del año anterior me muestra el año completo y no hasta la fecha, por ejemplo me muestra todo el 2016 pero no hasta septiembre (mes en curso) ojala me puedas ayudar

  7. buenas tardes

    estaría necesitando realizar una medida que me arroje , la suma de periodo actual del mes , contra el mes anterior en el mismo periodo . Es decir , los primeros 2 dias del mes actual , contra los 2 primeros dias del mes anterior .y que realice la comparativa a medida que avanza el mes actual.

    estoy hace tiempo con esto y no he encontrado la forma

  8. Buenas tardes, muy buena la información… Una consulta, habrá una manera o una función que me permita comparar la venta actual a la fecha, con la venta del año anterior de ese mismo día. Por ejemplo: Tengo que al 5-Oct.-2017 mi venta es $1,000,000 y la quiero hacer la comparación de cuanto fue mi venta al 5-Oct.-2016.
    Hay una función que me pueda ayudar con esto??

  9. Cuando alguien no pone los precios del producto o servicio que vende yo desconfío muchísimo.
    Por otro lado me parece de muy mal gusto usar un foro de un programa para hacer publicidad de otro

  10. Buenos días,

    tengo un problema al hacer lo de las semanas y es que siempre te saca como que existen 53 semanas y en realidad no es así. Por ejemplo, el día 1 de enero de 2016 me lo asigna a la primera semana del 2017 y en realidad debería pertenecer a la 53 del 2016.

    ¿Alguien ha solucionado esta problemática?

  11. Buenos días,

    Situación: Tengo datos de los ingresos diarios de mi compañía.

    Me encantaría crear una nueva medida, que me diga la media de los ingresos de los últimos 14 días, para cada día. Y poder dibujar la línea en el gráfico.

    En excel es muy fácil, pero no consigo dar con la función en power bi.

  12. Buen día,

    Estoy trabajando con inteligencia de Fechas y haciendo los comparativos para el corto, mediano y largo plazo, ya tengo el corto (mes vs mes o mes vs mes AA) mediano (YTD vs LY) pero, al hacer el largo plazo consigo tener solo MAT, el del año anterior no me sale podrías ayudarme?

    tengo esto para MAT

    = CALCULATE([SOSI Values T],DATESINPERIOD(Periodos[Fecha],LASTDATE(Periodos[Fecha]),-1,YEAR))

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.