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:

  1. Importa la tabla a un modelo de datos en un archivo nuevo de Power BI.
  2. Crea una segmentación por año.
  3. Agrega una matriz con los datos de Producto y Categoría como filas y el monto de Ventas.
  4. 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.
  5. 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().
  6. Crea un gráfico que muestre las ventas y las compare con la meta según su categoría.
  7. 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.
  8. 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.
  9. 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.