Cómo hacer comparaciones de fechas en Power Bi

Foto del autor
Juan Camilo Jiménez

Lectura: 2 minutos

Power Bi para todos

Cargar el archivo Excel de prueba para hacer el modelo

Se realizar la conexión por el «método web» del archivo de Excel que dejo a continuación para construir el modelo y poder desarrollar el ejercicio:

Crear la función: CreatePeriodTable

Fuente: Chris Webb

Con el Power query abierto, vamos a crear una consulta en blanco y pegamos lo siguiente:

crear una consulta en blanco en power query y pegamos lo siguiente

Se crear una consulta nueva llamada «consulta1» > luego le damos en el «editor avanzado» > en la ventana que se abre debe de borran los datos que salen por default (origen y esas cosas), cuanto esté el lienzo en blanco entonces copian y pegas el siguiente Script:

copian y pegas el siguiente Script en el editor avanzado de power query

Script que deben copiar y pegar:

(
PeriodName as text, 
StartDate as date, 
EndDate as date, 
SortOrder as number
) as table =>
let
    DayCount = Duration.Days(EndDate-StartDate)+1,
    DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    AddPeriodName = List.Transform(DateList, 
        each {PeriodName,_,SortOrder}),
    CreateTable = #table(
        type table[Period=text, Date=date, Sort=number], 
        AddPeriodName)
in
    CreateTable

Así debe quedar el script del editor avanzado de power query:

editor avanzado de power query creando la consulta periodos

y le dan en Listo.

Deben cambiar el nombre de la consulta1

Se Cambia el nombre de la consulta recién creada de «consulta1» a “CreatePeriodTable”

Cambiar nombre de consultas en power query

Se crea la planeación de los periodos que voy a comparar con “Lenguaje M”:

Período ActualPeríodo Anterior
Día (Hoy)Día (Ayer)
Semana (inicio y fin de semana actual)Semana (inicio y fin de semana anterior)
Mes (inicio y fin de Mes actual)Mes (inicio y fin de Mes anterior)
Trimestre (Inicio y fin de trimestre actual)Trimestre (Inicio y fin de trimestre acterior)
Semestre (Inicio y fin de semestre actual)Semestre (Inicio y fin de semestre anterior)
Año  (Inicio y fin de año actual)Año  (Inicio y fin de año anterior)

Copiar y pegar el siguiente script en el editor de power query

En este paso vamos a Crear una nueva consulta en blanco, la cual llamaremos “DimPeriodos”. Pero le pueden dar el nombre que quieran.

Ahora, vamos al editor avanzado de power query seleccionando obviamente la consulta en blanco que acabamos de crear «DimPeriodos» y copiamos y pegamos el siguiente script, para crear los periodos que hemos definido.

En esta parte hay que aclara que cada persona crea los periodos que necesiten, yo les dejo los que creí importantes para este ejercicio. También les delo en los siguientes enlaces más blogs donde pueden investigar como crear mas períodos según sus necesidades.

let
//---------------------------------------------------------------------------
// link de funciones LENGUAJE M   https://docs.microsoft.com/en-us/powerquery-m/date-functions

// Identificar día hoy
   Hoy = Date.From (DateTimeZone.FixedUtcNow() - #duration(0,5,0,0)),
 
//Identificar Ayer
    Ayer = Hoy - #duration(1,0,0,0),
 
//---------------------------------------------------------------------------
 
// Identificar inicio de semana y fin de semana - Actual
    InicioSemana = Date.StartOfWeek (Hoy,Day.Monday),
    FinSemana = Date.EndOfWeek(InicioSemana,Day.Monday),
 
// Identificar inicio de samana y fin de semana - anterior
    InicioSemanaPasada = InicioSemana - #duration ( 7,0,0,0 ),
    FinSemanaPasada = FinSemana - #duration ( 7,0,0,0 ),
 
//---------------------------------------------------------------------------
 
// Identificar inicio de mes y fin de mes - Actual
    InicioMes = Date.From(Date.StartOfMonth(Hoy)),
    FinMes = Date.From(Date.EndOfMonth(Hoy)),
 
// Identificar inicio de mes y fin de mes - anterior
    InicioMesPasado = Date.AddMonths(InicioMes, -1),
    FinMesPasado = Date.EndOfMonth(Date.AddMonths(Hoy,-1)),
 
//---------------------------------------------------------------------------

// Identificar inicio de trimestre y fin de trimestre - Actual (para sacar semestreste debemos identificar el trimestre)
    InicioTrimestre = Date.From(Date.StartOfQuarter(Hoy)),
    FinTrimestre = Date.From(Date.EndOfQuarter(Hoy)),
 
// Identificar inicio de trimestre y fin de trimestre - anterior
    InicioTrimestrePasado = Date.AddQuarters(InicioTrimestre, -1),
    FinTrimestrePasado = Date.AddQuarters(FinTrimestre, -1),
 
//---------------------------------------------------------------------------

// Identificar inicio de semestre y fin de semestre - Actual
    InicioSemestre = Date.AddQuarters(InicioTrimestre,-1),
    FinSemestre = Date.AddQuarters(InicioSemestre,2) - #duration(1,0,0,0) ,
 
// Identificar inicio de semestre y fin de semestre - anterior
    InicioSemestrePasado = Date.AddQuarters(InicioSemestre, -2),
    FinSemestrePasado = Date.AddQuarters(FinSemestre, -2),
 
//---------------------------------------------------------------------------
 
// Identificar inicio de año y fin de año - Actual
    InicioAño = Date.From ( Date.StartOfYear (Hoy) ),
    FinAño = Date.From ( Date.EndOfYear (Hoy) ),
 
//---------------------------------------------------------------------------
 
 
    Ranges = {
//Período Hoy, ayer y hace 2 días
                {"Hoy",
                Hoy,
                Hoy,
                1},
 
                {"Ayer",
                Ayer,
                Ayer,
                2},
 
                 {"Hace 2 días",
                Hoy - #duration(2,0,0,0),
                Hoy - #duration(2,0,0,0),
                3}, 

//Período Semanas de este año
                {"Semana actual",
                InicioSemana,
                Hoy,
                4},

                {"Semana anterior hasta la fecha",
                InicioSemanaPasada, 
                Hoy - #duration(7,0,0,0),
                5},

//Período meses año actual
                {"Mes actual",
                InicioMes,
                Hoy,
                6},

                {"Mes anterior hasta la fecha",
                InicioMesPasado,
                Date.AddMonths(Hoy, -1),
                7},

//Período semestre año actual
                {"Semestre actual",
                InicioSemestre ,
                Hoy,
                8},
 
                {"Semestre anterior hasta la fecha",
                InicioSemestrePasado,
                Date.AddQuarters(Hoy, -2),
                9},

//Período año actual
                {"Año actual",
                InicioAño,
                Hoy,
                10},
 
                {"Año anterior hasta la fecha",
                Date.AddYears(InicioAño, -1),
                Date.AddYears(Hoy, -1),
                11},

//Período últimos 7 dias y 7 días inmediatamente anteriores
                {"Últimos 7 días",
                Date.AddDays(Ayer,-6),
                Ayer,
                12},
 
                {"7 días anteriores",
                Date.AddDays(Ayer,-13),
                Date.AddDays(Ayer,-7),
                13},

//Período últimos 30 dias y los 30 días inmediatamente anteriores
                {"Últimos 30 días",
                Date.AddDays(Ayer,-29),
                Ayer,
                14},
 
                {"30 días anteriores",
                Date.AddDays(Ayer,-59),
                Date.AddDays(Ayer,-30),
                15}

             },
    GetTables = List.Transform(Ranges,
            each CreatePeriodTable(_{0}, _{1}, _{2}, _{3})),
    Output = Table.Combine(GetTables),
    #"Filas filtradas" = Table.SelectRows(Output, each true)
in
    #"Filas filtradas"

Link fuente de investigación: script anterior aquí + el de Chris Webb

Debe quedar así:

como crear en lenguaje M de power query una consulta para comparar fechas

Aplicamos y Cerramos Power query

Ahora damos Aplicar y Cerrar en Power query y listo, llegamos a Power Bi para hacer los siguientes pasos.

Creamos la tabla calendario si aun no la tienen creada.

Este paso simplemente dejo los link para que vean los vídeos de mi curso donde explico el paso a paso de cómo crear una tabla calendario en power bi. (esto con el fin de no perder tiempo en cosas que ya se han explicado en el curso)

DimCalendario = 
    //Suscríbete a Tutoliber en youtube y apoya mi trabajo.
    Var MinYear = YEAR(MIN(tb_ventas[FECHA VENTA]))
    Var Fecha =  FILTER(CALENDARAUTO();YEAR([Date]) >=MinYear )
        RETURN
        SELECTCOLUMNS   (
            Fecha;
                    "Fecha"; [Date];
                    "Año"; YEAR([Date]);
                    "Mes Num"; MONTH([Date]);
                    "Mes"; FORMAT([Date]; "mmm");
                    "Día"; DAY([Date]);
                    "Día de la Semana"; WEEKDAY([Date];2);
                    "Nombre Día"; FORMAT([Date]; "dddd");
                    "Trim Num"; ROUNDUP(MONTH([Date])/3;0);
                    "Trim Tex"; SWITCH([Date];
                                1;"T1";
                                2;"T2";
                                3; "T3";
                                "T4");
                    "Año y Trim"; YEAR([Date])&"-"& SWITCH(ROUNDUP(MONTH([Date])/3;0);
                                1;"T1";
                                2;"T2";
                                3; "T3";
                                "T4");
                    "Num Semana"; WEEKNUM([Date];2);
                    "Semana ISO en DAX"; ROUNDDOWN(([Date] - DATE (YEAR([Date] - WEEKDAY ([Date]-1)+4);1;3)
                                        +WEEKDAY(DATE(YEAR([Date]-WEEKDAY([Date]-1)+4);1;3))+5)/7;0)
                )

Debe quedar así:

Como crear una tabla calendario con power bi en automatica

Hacer relación entre la tabla “DimPeriodos” y tu tabla calendario que en mi caso se llama “DimCalendario”

Debemos crear una relación en AMBAS direcciones del filtro cruzado entre DimCalendario “fecha” 1—-< >—- * DimPeríodos “date”

Hacer relación en ambas direcciones del filtro crusado entre la tabla DimPeriodos y DimCalendario

Crear tabla de “ComparacionDePeriodos” en power query

Para esto como ayuda se crear una tablita Excel en la cual creamos todos los periodos y comparativas que vamos deseamos realizar. (el archivo está en la carpeta descargas al final del artículo)

Tabla de excel para comparar periodos en power bi

Ingresamos a power query y procedemos y pasamos a ejecutar merge o combinar consulta:

Seleccionamos la consulta «ComparacionDePeriodos«, luego seleccionamos el botón «combinar consulta o merge» y hacemos el cruce entre la columna “Período actual” de la base “ComparacionDePeriodos” vs. La columna “Periodo” de la tabla “DimPeriodos” y explotamos la columna “Date” y esta luego se cambia de nombre por “Fecha Período Actual”

Luego se hace otro merge:

Con la columna “Período anterior” de la base “ComparacionDePeriodos” vs. La columna “Periodo” de la tabla “DimPeriodos” y explotamos la columna “Date” y esta luego se cambia de nombre por “Fecha Período Anterior”

Debemos hacer las siguientes relaciones:

DimCalendario “fecha” 1 —-< AMBAS DIR >—- * ComparacionDePeriodos “Período Actual”

DimCalendario “fecha” 1 —-< UNICA DIR —- * ComparacionDePeriodos “Período Anterior”

Relaciones entre tabla calendario dim periodos y compración de periodos

Ahora debemos hacer las siguientes medidas:

Ahora para poder comparar los períodos que acabamos de crear debemos crear las siguientes medidas:

medidas DAX para comparar fechas
Facturado = SUMX(tb_ventas;tb_ventas[CANT]*tb_ventas[VR UNITARIO])

TIENE UN VALOR = 
HASONEVALUE( ComparacionDePeriodos[Período actual vs. Anterior])
//Devuelve true si tiene seleccionado un valor o filtro.

Facturado PA = 
IF (
[TIENE UN VALOR] = TRUE ();
CALCULATE (
[Facturado];
USERELATIONSHIP ( DimCalendario[Fecha]; ComparacionDePeriodos[Fecha Período Anterior] );
ALL ( DimCalendario[Fecha])
);
0
)


Facturado Var = 
IF ( [TIENE UN VALOR] = TRUE (); [Facturado] - [Facturado PA]; 0 )


Facturado Var % = 
IF ( [TIENE UN VALOR] = TRUE (); DIVIDE ( [Facturado Var]; [Facturado PA] ); 0 )

Crear Medidas para Titulo dinámico

HastaDia = 
VAR NombreDia = WEEKDAY(MAX(DimCalendario[Fecha]);2) 
     
RETURN 
  SWITCH(
        TRUE();
        NombreDia=1;"Lun";
        NombreDia=2;"Mar";
        NombreDia=3;"Mié";
        NombreDia=4;"Jue";
        NombreDia=5;"Vie";
        NombreDia=6;"Sáb";
        NombreDia=7;"Dom"
    )

DesdeDia = 
VAR NombreDia = WEEKDAY(MAX(ComparacionDePeriodos[Fecha Período Anterior]);2) 
     
RETURN 
  SWITCH(
        TRUE();
        NombreDia=1;"Lun";
        NombreDia=2;"Mar";
        NombreDia=3;"Mié";
        NombreDia=4;"Jue";
        NombreDia=5;"Vie";
        NombreDia=6;"Sáb";
        NombreDia=7;"Dom"
    )


Titulo Dinamico = 
VAR FechaMin = MIN(DimCalendario[Fecha])
VAR FechaMax = MAX(DimCalendario[Fecha])
    RETURN
    "Desde el " & 
        [DesdeDia]& " "&
        FORMAT(FechaMin;"d/m/yy") & " al " & [HastaDia] &" " &
        FORMAT(FechaMax;"d/m/yy") & "" & " (" &
        FORMAT( 
            DATEDIFF ( FechaMin ; FechaMax + 1 ; DAY ) ; "#,0" ) & " días)"

¿De cuánta utilidad te ha parecido este contenido?

¡Haz clic en una estrella para puntuarlo!

Promedio de puntuación 5 / 5. Recuento de votos: 1

Hasta ahora, ¡no hay votos!. Sé el primero en puntuar este contenido.

Ya que has encontrado útil este contenido...

¡Sígueme en los medios sociales!