Tema

Contenido

Objetivo

Agregar nuevas medidas a través de funciones DAX a tus tablas, para manejar el tiempo y tus metas de manera inteligente.

Descripción

En este tema aprenderás a utilizar algunas funciones que te otorgarán bases más sólidas en el uso inteligente del tiempo, así como la manera de segmentar los datos en niveles.

Explicación

Haz clic para revisar la explicación.

Prorrateo de Metas

Recursos adicionales

Los siguientes enlaces son externos a la Universidad Tecmilenio, al acceder a ellos considera que debes apegarte a sus términos y condiciones.

Revisa el siguiente video:

Para conocer más sobre Isinscope y otras funciones similares, revisa el siguiente video:

Sandielly Ortega. (2021, 17 de Marzo). Diferencia entre las funciones ISINSCOPE Y HASONEVALUE en Power BI [Archivo de video]. Recuperado de https://www.youtube.com/watch?v=x6tamxTeJjI

Revisa la siguiente lectura:

Para conocer más sobre otras funciones relacionadas con uso inteligente del tiempo, te recomendamos leer:

Interactive Chaos. (2018). STARTOFMONTH. Recuperado de https://interactivechaos.com/es/dax/function/startofmonth

Actividad

Objetivo

Demostrar el uso de las funciones ISINSCOPE y las funciones de inteligencia de tiempo.

Instrucciones

Utilizando los datos de la tabla anexada de Ventas por Producto, realiza lo siguiente:

Importa la tabla a un modelo de datos en un archivo nuevo de Power BI.
Crea una segmentación por año.
Agrega una matriz con los datos de Producto y Categoría como filas y el monto de Ventas.
Utilizando la función ISINSCOPE crea una medida que permita reconocer si la categoría se encuentra como un nivel jerárquico de una tabla.
Establece como meta de ventas por categoría el promedio registrado en año anterior. Como recomendación puedes agregar la columna del año anterior o del siguiente año con las funciones PREVIOUSYEAR(), NEXTYEAR().
Crea un gráfico que muestre las ventas y las compare con la meta según su categoría.
Crea un calendario automático que contenga las fechas de inicio y fin de: mes, trimestre y año, mediante las funciones STARTOFMONTH(), ENDOFMONTH(), STARTOFQUARTER(), ENDOFQUARTER(), STARTOFYEAR,y ENDOFYEAR () respectivamente.
Usando el calendario, agrega una columna a la tabla que incluya el día, mes y año del último día del año según el año de venta.
Incluye esta información a la visualización de la matriz.

Tabla Ventas por Producto:

Year	Category	Product	Sales Amount
2017	Bikes	Mountain-100 Black, 38	$706,863.31
2018	Bikes	Mountain-100 Black, 38	$633,133.95
2017	Bikes	Mountain-100 Black, 42	$674,393.20
2018	Bikes	Mountain-100 Black, 42	$580,329.53
2017	Bikes	Mountain-100 Black, 44	$746,794.84
2018	Bikes	Mountain-100 Black, 44	$619,057.54
2017	Bikes	Mountain-100 Black, 48	$633,823.12
2018	Bikes	Mountain-100 Black, 48	$600,452.91
2017	Bikes	Mountain-100 Silver, 38	$708,557.92
2018	Bikes	Mountain-100 Silver, 38	$583,310.79
2017	Bikes	Mountain-100 Silver, 42	$603,014.07
2018	Bikes	Mountain-100 Silver, 42	$583,480.78
2017	Bikes	Mountain-100 Silver, 44	$635,939.57
2018	Bikes	Mountain-100 Silver, 44	$581,270.79
2017	Bikes	Mountain-100 Silver, 48	$506,598.51
2018	Bikes	Mountain-100 Silver, 48	$513,058.49
2018	Bikes	Mountain-200 Black, 38	$878,889.35
2019	Bikes	Mountain-200 Black, 38	$2,193,287.04
2020	Bikes	Mountain-200 Black, 38	$1,328,416.41
2018	Bikes	Mountain-200 Black, 42	$918,525.26
2019	Bikes	Mountain-200 Black, 42	$1,943,737.04
2020	Bikes	Mountain-200 Black, 42	$1,147,232.45
2018	Bikes	Mountain-200 Black, 46	$690,696.08
2019	Bikes	Mountain-200 Black, 46	$1,640,032.63
2020	Bikes	Mountain-200 Black, 46	$978,944.51
2018	Bikes	Mountain-200 Silver, 38	$753,842.64
2019	Bikes	Mountain-200 Silver, 38	$1,806,924.88
2020	Bikes	Mountain-200 Silver, 38	$1,132,910.51
2018	Bikes	Mountain-200 Silver, 42	$830,725.45
2019	Bikes	Mountain-200 Silver, 42	$1,618,075.14
2020	Bikes	Mountain-200 Silver, 42	$989,678.27
2018	Bikes	Mountain-200 Silver, 46	$788,882.78
2019	Bikes	Mountain-200 Silver, 46	$1,659,355.22
2020	Bikes	Mountain-200 Silver, 46	$986,018.95
2018	Bikes	Mountain-300 Black, 38	$216,054.59
2019	Bikes	Mountain-300 Black, 38	$226,422.50
2018	Bikes	Mountain-300 Black, 40	$262,062.17
2019	Bikes	Mountain-300 Black, 40	$239,586.71
2018	Bikes	Mountain-300 Black, 44	$248,181.70
2019	Bikes	Mountain-300 Black, 44	$235,869.82
2018	Bikes	Mountain-300 Black, 48	$242,349.76
2019	Bikes	Mountain-300 Black, 48	$236,722.14
2019	Bikes	Mountain-400-W Silver, 38	$112,499.44
2020	Bikes	Mountain-400-W Silver, 38	$129,274.32
2019	Bikes	Mountain-400-W Silver, 40	$166,112.27
2020	Bikes	Mountain-400-W Silver, 40	$157,591.55
2019	Bikes	Mountain-400-W Silver, 42	$99,418.11
2020	Bikes	Mountain-400-W Silver, 42	$118,039.77
2019	Bikes	Mountain-400-W Silver, 46	$100,997.41
2019	Components	Chain	$5,352.94
2020	Components	Chain	$4,024.77
2019	Components	Front Brakes	$29,467.91
2020	Components	Front Brakes	$20,831.40
2019	Components	Front Derailleur	$25,037.37
2020	Components	Front Derailleur	$19,446.89
2019	Components	HL Bottom Bracket	$21,212.15
2020	Components	HL Bottom Bracket	$18,369.29
2019	Components	HL Crankset	$82,771.21
2020	Components	HL Crankset	$65,851.37
2018	Components	HL Fork	$34,735.06
2019	Components	HL Fork	$26,299.55
2018	Components	HL Headset	$9,579.26
2019	Components	HL Headset	$9,504.43
2017	Components	HL Mountain Frame - Black, 38	$50,029.30
2018	Components	HL Mountain Frame - Black, 38	$140,074.91
2019	Components	HL Mountain Frame - Black, 38	$203,176.15
2020	Components	HL Mountain Frame - Black, 38	$108,507.84
2017	Components	HL Mountain Frame - Black, 42	$62,179.27
2018	Components	HL Mountain Frame - Black, 42	$268,971.84
2019	Components	HL Mountain Frame - Black, 42	$392,291.92
2020	Components	HL Mountain Frame - Black, 42	$178,147.20
2017	Components	HL Mountain Frame - Black, 44	$7,287.84
2018	Components	HL Mountain Frame - Black, 44	$6,478.08
2017	Components	HL Mountain Frame - Black, 48	$57,492.96
2018	Components	HL Mountain Frame - Black, 48	$48,585.60
2017	Components	HL Mountain Frame - Silver, 38	$64,310.95
2018	Components	HL Mountain Frame - Silver, 38	$283,806.36
2019	Components	HL Mountain Frame - Silver, 38	$402,549.38
2020	Components	HL Mountain Frame - Silver, 38	$180,114.00
2017	Components	HL Mountain Frame - Silver, 42	$9,393.73
2018	Components	HL Mountain Frame - Silver, 42	$106,192.54
2019	Components	HL Mountain Frame - Silver, 42	$125,633.24
2020	Components	HL Mountain Frame - Silver, 42	$28,654.50
2017	Components	HL Mountain Frame - Silver, 46	$51,304.24
2018	Components	HL Mountain Frame - Silver, 46	$135,732.22
2019	Components	HL Mountain Frame - Silver, 46	$212,638.71
2020	Components	HL Mountain Frame - Silver, 46	$99,881.40
2017	Components	HL Mountain Frame - Silver, 48	$69,589.50
2018	Components	HL Mountain Frame - Silver, 48	$72,045.60
2018	Components	HL Mountain Front Wheel	$31,162.32
2019	Components	HL Mountain Front Wheel	$8,826.32
2018	Components	HL Mountain Handlebars	$11,939.53
2019	Components	HL Mountain Handlebars	$17,528.80
2020	Components	HL Mountain Handlebars	$4,907.02
2019	Components	HL Mountain Pedal	$21,312.26
2020	Components	HL Mountain Pedal	$16,706.07
2018	Components	HL Mountain Rear Wheel	$92,181.31
2019	Components	HL Mountain Rear Wheel	$73,831.98
2019	Components	HL Mountain Seat/Saddle	$9,854.21
2017	Clothing	AWC Logo Cap	$2,686.87
2018	Clothing	AWC Logo Cap	$9,121.74
2019	Clothing	AWC Logo Cap	$22,914.78
2020	Clothing	AWC Logo Cap	$16,506.05
2019	Clothing	Classic Vest, L	$5,346.70
2020	Clothing	Classic Vest, L	$7,493.00
2019	Clothing	Classic Vest, M	$53,466.88
2020	Clothing	Classic Vest, M	$36,783.72
2019	Clothing	Classic Vest, S	$84,020.59
2020	Clothing	Classic Vest, S	$72,377.48
2018	Clothing	Full-Finger Gloves, L	$38,373.65
2019	Clothing	Full-Finger Gloves, L	$31,248.55
2018	Clothing	Full-Finger Gloves, M	$26,180.54
2019	Clothing	Full-Finger Gloves, M	$21,807.40
2018	Clothing	Full-Finger Gloves, S	$6,701.44
2019	Clothing	Full-Finger Gloves, S	$4,536.01
2018	Clothing	Half-Finger Gloves, L	$2,543.20
2019	Clothing	Half-Finger Gloves, L	$12,213.89
2020	Clothing	Half-Finger Gloves, L	$8,155.17
2018	Clothing	Half-Finger Gloves, M	$7,757.49
2019	Clothing	Half-Finger Gloves, M	$30,283.69
2020	Clothing	Half-Finger Gloves, M	$16,504.31
2018	Clothing	Half-Finger Gloves, S	$3,772.42
2019	Clothing	Half-Finger Gloves, S	$20,790.22
2020	Clothing	Half-Finger Gloves, S	$11,927.91
2017	Clothing	Long-Sleeve Logo Jersey, L	$14,758.27
2018	Clothing	Long-Sleeve Logo Jersey, L	$52,227.74
2019	Clothing	Long-Sleeve Logo Jersey, L	$87,124.37
2020	Clothing	Long-Sleeve Logo Jersey, L	$44,644.60
2017	Clothing	Long-Sleeve Logo Jersey, M	$6,316.05
2018	Clothing	Long-Sleeve Logo Jersey, M	$28,269.55
2019	Clothing	Long-Sleeve Logo Jersey, M	$51,246.89
2020	Clothing	Long-Sleeve Logo Jersey, M	$29,416.74
2019	Clothing	Long-Sleeve Logo Jersey, S	$8,648.27
2020	Clothing	Long-Sleeve Logo Jersey, S	$12,797.44
2017	Clothing	Long-Sleeve Logo Jersey, XL	$7,181.26
2018	Clothing	Long-Sleeve Logo Jersey, XL	$26,870.06
2019	Clothing	Long-Sleeve Logo Jersey, XL	$40,674.06
2020	Clothing	Long-Sleeve Logo Jersey, XL	$20,885.82
2018	Clothing	Men's Bib-Shorts, L	$22,245.53
2019	Clothing	Men's Bib-Shorts, L	$11,932.67
2018	Clothing	Men's Bib-Shorts, M	$50,778.77
2019	Clothing	Men's Bib-Shorts, M	$34,802.34
2018	Clothing	Men's Bib-Shorts, S	$26,246.70
2019	Clothing	Men's Bib-Shorts, S	$20,733.70
2018	Clothing	Men's Sports Shorts, L	$10,186.30
2019	Clothing	Men's Sports Shorts, L	$5,831.03
2018	Clothing	Men's Sports Shorts, M	$26,608.10
2019	Clothing	Men's Sports Shorts, M	$18,908.20
2018	Clothing	Men's Sports Shorts, S	$10,631.48
2020	Accessories	Fender Set - Mountain	$24,397.80
2019	Accessories	Hitch Rack - 4-Bike	$132,876.47
2020	Accessories	Hitch Rack - 4-Bike	$104,219.69
2019	Accessories	HL Mountain Tire	$23,065.00
2020	Accessories	HL Mountain Tire	$25,795.00
2019	Accessories	HL Road Tire	$14,670.00
2020	Accessories	HL Road Tire	$13,300.80
2019	Accessories	Hydration Pack - 70 oz.	$59,282.73
2020	Accessories	Hydration Pack - 70 oz.	$46,543.68
2019	Accessories	LL Mountain Tire	$10,170.93
2020	Accessories	LL Mountain Tire	$11,370.45
2019	Accessories	LL Road Tire	$10,487.12
2020	Accessories	LL Road Tire	$11,948.44
2018	Accessories	Minipump	$8,081.41
2019	Accessories	Minipump	$5,433.28
2019	Accessories	ML Mountain Tire	$17,304.23
2020	Accessories	ML Mountain Tire	$17,514.16
2019	Accessories	ML Road Tire	$10,820.67
2020	Accessories	ML Road Tire	$12,320.07
2019	Accessories	Mountain Bottle Cage	$9,430.56
2020	Accessories	Mountain Bottle Cage	$10,799.19
2019	Accessories	Mountain Tire Tube	$7,475.02
2020	Accessories	Mountain Tire Tube	$7,969.03
2019	Accessories	Patch Kit/8 Patches	$4,141.28
2020	Accessories	Patch Kit/8 Patches	$4,091.31
2019	Accessories	Road Bottle Cage	$7,227.96
2020	Accessories	Road Bottle Cage	$8,162.92
2019	Accessories	Road Tire Tube	$4,540.62
2020	Accessories	Road Tire Tube	$4,939.62
2017	Accessories	Sport-100 Helmet, Black	$6,681.73
2018	Accessories	Sport-100 Helmet, Black	$24,213.18
2019	Accessories	Sport-100 Helmet, Black	$73,243.80
2020	Accessories	Sport-100 Helmet, Black	$56,730.82
2017	Accessories	Sport-100 Helmet, Blue	$7,114.14
2018	Accessories	Sport-100 Helmet, Blue	$25,759.91
2019	Accessories	Sport-100 Helmet, Blue	$73,271.95
2020	Accessories	Sport-100 Helmet, Blue	$59,260.62
2017	Accessories	Sport-100 Helmet, Red	$6,439.49
2018	Accessories	Sport-100 Helmet, Red	$22,586.08
2019	Accessories	Sport-100 Helmet, Red	$71,506.74
2020	Accessories	Sport-100 Helmet, Red	$57,240.08
2019	Accessories	Touring Tire	$11,943.88
2020	Accessories	Touring Tire	$15,161.77
2019	Accessories	Touring Tire Tube	$3,358.27
2020	Accessories	Touring Tire Tube	$4,066.85
2019	Accessories	Water Bottle - 30 oz.	$14,216.72
2020	Accessories	Water Bottle - 30 oz.	$14,437.45

Checklist

Asegúrate de:

Incluir medidas usando ISINSCOPE() para establecer si la columna pertenece al nivel jerárquico.
Establecer metas de ventas basándose en el promedio de las ventas registradas en año previo.
Diseñar un gráfico que compare las ventas en relación a la meta establecida.
Incluir un calendario automático tomando en cuenta las fechas de inicio y fin del año, trimestre y mes.
Presentar una matriz que muestra la Categoría, Producto, Venta, Meta y último día del año.