Comprensión de las tablas de datos

El análisis de datos implica examinar datos a lo largo del tiempo y realizar cálculos a lo largo de períodos de tiempo. Por ejemplo, es posible que deba comparar las ganancias del año actual con las ganancias del año anterior. Del mismo modo, es posible que deba pronosticar el crecimiento y las ganancias en los próximos años. Para estos, debe utilizar agrupaciones y agregaciones durante un período de tiempo.

DAX proporciona varias funciones de inteligencia de tiempo que le ayudan a realizar la mayoría de estos cálculos. Sin embargo, estas funciones de DAX requieren una tabla de fecha para su uso con las otras tablas en el modelo de datos.

Puede importar una tabla de fecha junto con otros datos de una fuente de datos o puede crear una tabla de fecha usted mismo en el modelo de datos.

En este capítulo, comprenderá los diferentes aspectos de las tablas de fechas. Si está familiarizado con las tablas de fecha en el modelo de datos de Power Pivot, puede omitir este capítulo y continuar con los capítulos siguientes. De lo contrario, puede comprender las tablas de fecha en el modelo de datos de Power Pivot.

¿Qué es una tabla de fechas?

Una tabla de fechas es una tabla en un modelo de datos, con al menos una columna de fechas contiguas a lo largo de una duración requerida. Puede tener columnas adicionales que representen diferentes períodos de tiempo. Sin embargo, lo que se necesita es la columna de fechas contiguas, como lo requieren las funciones de DAX Time Intelligence.

Por ejemplo,

  • Una tabla de fecha puede tener columnas como Fecha, Mes fiscal, Trimestre fiscal y Año fiscal.

  • Una tabla de fecha puede tener columnas como Fecha, Mes, Trimestre y Año.

Tabla de fechas con fechas contiguas

Suponga que debe realizar cálculos en el rango de un año calendario. Luego, la tabla Fecha debe tener al menos una columna con un conjunto contiguo de fechas, incluidas todas las fechas de ese año calendario específico.

Por ejemplo, supongamos que los datos que desea examinar tiene fecha del 1 de abril, 2014 a noviembre 30 de XX de 2016.

  • Si usted tiene que reportar en un año calendario, también necesita una tabla con una columna Fecha - Date, que contiene todas las fechas a partir de enero 1 st , 2014 a diciembre 31 de st , 2016 en una secuencia.

  • Si usted tiene que informe sobre un año fiscal, y el cierre del ejercicio es de 30 º de junio también necesita una tabla fecha con una columna - Fecha, que contiene todas las fechas a partir de julio 1 st , 2013 a junio 30 ª de 2017 en una secuencia.

  • Si tiene que informar tanto del año calendario como del año fiscal, puede tener una única tabla de fechas que abarque el rango de fechas requerido.

Su tabla de fecha debe contener todos los días para el rango de cada año en la duración dada. Por lo tanto, obtendrá fechas contiguas dentro de ese período de tiempo.

Si actualiza regularmente sus datos con datos nuevos, la fecha de finalización se extenderá uno o dos años, de modo que no tenga que actualizar su tabla de fechas con frecuencia.

Una tabla de fechas se parece a la siguiente captura de pantalla.

Agregar una tabla de fechas al modelo de datos

Puede agregar una tabla de fechas al modelo de datos de cualquiera de las siguientes formas:

  • Importación desde una base de datos relacional o cualquier otra fuente de datos.

  • Crear una tabla de fechas en Excel y luego copiar o vincular a una nueva tabla en Power Pivot.

  • Importación de Microsoft Azure Marketplace.

Crear una tabla de fechas en Excel y copiar al modelo de datos

Crear una tabla de fecha en Excel y copiarla en el modelo de datos es la forma más fácil y flexible de crear una tabla de datos en el modelo de datos.

  • Abra una nueva hoja de trabajo en Excel.

  • Tipo: fecha en la primera fila de una columna.

  • Escriba la primera fecha del rango de fechas que desea crear en la segunda fila de la misma columna.

  • Seleccione la celda, haga clic en el controlador de relleno y arrástrelo hacia abajo para crear una columna de fechas contiguas en el rango de fechas requerido.

Por ejemplo, escriba 1/1/2014, haga clic en el controlador de relleno y arrastre hacia abajo para completar las fechas contiguas hasta el 31/12/2016.

  • Haga clic en la columna Fecha.
  • Haga clic en la pestaña INSERTAR en la cinta.
  • Haga clic en Tabla.
  • Verifique el rango de la mesa.
  • Haga clic en Aceptar.

La tabla de una sola columna de fechas está lista en Excel.

  • Seleccione la mesa.
  • Haga clic en Copiar en la cinta.
  • Haga clic en la ventana de Power Pivot.
  • Haga clic en Pegar en la cinta.

Esto agregará el contenido del portapapeles a una nueva tabla en el modelo de datos. Por lo tanto, también puede usar el mismo método para crear una tabla de fecha en un modelo de datos existente.

El cuadro de diálogo Pegar vista previa aparece como se muestra en la siguiente captura de pantalla.

  • Escriba Fecha en el cuadro Nombre de la tabla.
  • Obtenga una vista previa de los datos.
  • Marque la casilla: use la primera fila como encabezados de columna.
  • Haga clic en Aceptar.

Esto copia el contenido del portapapeles en una nueva tabla en el modelo de datos.

Ahora, tiene una tabla de fechas en el modelo de datos con una sola columna de fechas contiguas. El encabezado de la columna es Fecha, tal como lo indicó en la tabla de Excel.

Agregar nuevas columnas de fecha a la tabla de fechas

A continuación, puede agregar columnas calculadas a la tabla Fecha según el requisito de sus cálculos.

Por ejemplo, puede agregar columnas: día, mes, año y trimestre de la siguiente manera:

  • Day

    =DAY('Date'[Date])

  • Month

    =MONTH('Date'[Date])

  • Year

    =YEAR('Date'[Date])

  • Trimestre

    = CONCATENAR ("QTR", INT (('Fecha' [Mes] +2) / 3))

La tabla de fechas resultante en el modelo de datos se parece a la siguiente captura de pantalla.

Por lo tanto, puede agregar cualquier número de columnas calculadas a la tabla Fecha. Lo que es importante y obligatorio es que la tabla Fecha debe tener una columna de fechas contiguas que abarque la duración del tiempo durante el cual realiza los cálculos.

Creación de una tabla de fechas para un año calendario

Un año calendario normalmente incluye las fechas del 1 de enero al 31 de diciembre de un año y también incluye los días festivos marcados para ese año en particular. Al realizar cálculos, es posible que deba tener en cuenta solo los días laborables, excluidos los fines de semana y festivos.

Supongamos que desea crear una tabla de fechas para el año calendario 2017.

  • Crear una tabla de Excel con una Fecha de la columna, que consiste en fechas contiguas desde el 1 er enero de 2017 31 de st diciembre de 2017. (Consulte la sección anterior para saber cómo hacer esto).

  • Copie la tabla de Excel y péguela en una nueva tabla en el modelo de datos. (Consulte la sección anterior para saber cómo hacer esto).

  • Nombra la tabla como Calendario.

  • Agregue las siguientes columnas calculadas:

    • Día = DÍA ('Calendario' [Fecha])

    • Mes = MES ('Calendario' [Fecha])

    • Año = AÑO ('Calendario' [Fecha])

    • Día de la semana = FORMAT ('Calendario' [Fecha], "DDD")

    • Nombre del mes = FORMAT ('Calendario' [Fecha], "MMM")

Agregar días festivos a la tabla del calendario

Agregue días festivos a la tabla del calendario de la siguiente manera:

  • Obtenga la lista de feriados declarados para el año.

  • Por ejemplo, para los EE. UU., Puede obtener la lista de días festivos para cualquier año requerido en el siguiente enlace http://www.calendar-365.com/.

  • Cópielos y péguelos en una hoja de cálculo de Excel.

  • Copie la tabla de Excel y péguela en una nueva tabla en el modelo de datos.

  • Nombra la tabla como vacaciones.

  • A continuación, puede agregar una columna calculada de días festivos a la tabla Calendario utilizando la función DAX LOOKUPVALUE.

=LOOKUPVALUE(Holidays[Holiday],Holidays[Date],'Calendar'[Date])

La función DAX LOOKUPVALUE busca el tercer parámetro, es decir, Calendario [Fecha] en el segundo parámetro, es decir, Vacaciones [Fecha] y devuelve el primer parámetro, es decir, Vacaciones [Vacaciones] si hay una coincidencia. El resultado se verá como el que se muestra en la siguiente captura de pantalla.

Agregar columnas a un año fiscal

Un año fiscal general incluye las fechas desde el 1 er del mes después del final del año fiscal para el próximo cierre del ejercicio. Por ejemplo, si el cierre del ejercicio es de 31 st de marzo, luego el año fiscal varía de 1 er abril al 31 st marzo.

Puede incluir los períodos de tiempo fiscal en la tabla de calendario utilizando las fórmulas de DAX:

  • Agregar una medida para FYE

    FYE:=3

  • Agregue las siguientes columnas calculadas:

    • Fiscal Year

      = IF ('Calendario' [Mes] <= 'Calendario' [FYE], 'Calendario' [Año], 'Calendario' [Año] +1)

    • Fiscal Month

      = IF ('Calendario' [Mes] <= 'Calendario' [FYE], 12-'Calendario '[FYE] +' Calendario '[Mes],' Calendario '[Mes] -' Calendario '[FYE])

    • Fiscal Quarter

      = INT (('Calendario' [Mes fiscal] +2) / 3)

Configuración de la propiedad de la tabla de fechas

Cuando utiliza las funciones de DAX Time Intelligence como TOTALYTD, PREVIOUSMONTH y DATESBETWEEN, requieren que los metadatos funcionen correctamente. La propiedad de la tabla de fechas establece dichos metadatos.

Para establecer la propiedad de la tabla de fechas:

  • Seleccione la tabla Calendario en la ventana de Power Pivot.
  • Haga clic en la pestaña Diseño en la cinta.
  • Haga clic en Marcar como tabla de fechas en el grupo Calendarios.
  • Haga clic en Marcar como tabla de fechas en la lista desplegable.

Aparece el cuadro de diálogo Marcar como tabla de fechas. Seleccione la columna Fecha en la tabla Calendario. Esta debe ser la columna del tipo de datos Fecha y debe tener valores únicos. Haga clic en Aceptar.