Tiempo de lectura: 2 minutos
Contenido
- 1 Cargar el archivo Excel de prueba para hacer el modelo
- 2 Crear la función: CreatePeriodTable
- 3 Deben cambiar el nombre de la consulta1
- 4 Se crea la planeación de los periodos que voy a comparar con “Lenguaje M”:
- 5 Copiar y pegar el siguiente script en el editor de power query
- 6 Aplicamos y Cerramos Power query
- 7 Creamos la tabla calendario si aun no la tienen creada.
- 8 Hacer relación entre la tabla “DimPeriodos” y tu tabla calendario que en mi caso se llama “DimCalendario”
- 9 Crear tabla de “ComparacionDePeriodos” en power query
- 10 Debemos hacer las siguientes relaciones:
- 11 Ahora debemos hacer las siguientes medidas:
- 12 Crear Medidas para Titulo dinámico
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:
👉 https://tutoliberdesarrollador-my.sharepoint.com/personal/jpalomino_tutoliberdesarrollador_onmicrosoft_com/Documents/Power%20Bi%20para%20Youtube/Base%20Ejercicio%207.xlsx
Crear la función: CreatePeriodTable
Fuente: Chris Webb
Con el Power query abierto, vamos a crear una consulta en blanco 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:
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:
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”
Se crea la planeación de los periodos que voy a comparar con “Lenguaje M”:
| Período Actual | Perí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í:
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)
- ¿Cómo crear tabla calendario en power bi principiantes?
- 3 Formas Diferentes de Cómo crear una tabla calendario en Power Bi
- Y sino quieren ver los vídeos también les dejo a continuación el script para que copien y peguen en una nueva «Tabla Calculada«.
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í:
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”
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)
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”
Ahora debemos hacer las siguientes medidas:
Ahora para poder comparar los períodos que acabamos de crear debemos crear las siguientes medidas:
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)"
