Las tablas de fechas de Power Pivot son esenciales para examinar y calcular datos a lo largo del tiempo. En este artículo se proporciona una comprensión profunda de las tablas de fechas y de cómo puede crearlas en Power Pivot. En particular, este artículo describe:
- Por qué es importante una tabla de fechas para examinar y calcular datos por fechas y horas.
- Cómo usar Power Pivot para agregar una tabla de fechas al modelo de datos.
- Cómo crear nuevas columnas de fechas, como Año, Mes y Período, en una tabla de fechas.
- Cómo crear relaciones entre tablas de fechas y tablas de hechos.
- Cómo trabajar con el tiempo.
Este artículo está destinado a los usuarios que no están familiarizados con Power Pivot. Sin embargo, es importante que ya tenga un buen conocimiento de la importación de datos, la creación de relaciones y la creación de columnas y medidas calculadas.
En este artículo no se describe cómo usar las funciones de Time-Intelligence DAX en fórmulas de medida. Para obtener más información sobre cómo crear medidas con las funciones de Inteligencia de tiempo de DAX, consulte Inteligencia de tiempo en Power Pivot en Excel.
Nota
En Power Pivot, los nombres "medida" y "campo calculado" son sinónimos. Estamos usando el nombre medida a lo largo de este artículo. Para obtener más información, vea Medidas en Power Pivot.
Contenido
Descripción de las tablas de fechas
Casi todo el análisis de datos implica examinar y comparar datos sobre fechas y horas. Por ejemplo, puede que quieras sumar los importes de ventas del último trimestre fiscal y luego comparar esos totales con otros trimestres, o bien calcular un saldo de cierre de fin de mes de una cuenta. En cada uno de estos casos, se usan las fechas para agrupar y agregar transacciones de ventas o saldos durante un período de tiempo determinado.
Informe de Power View
Una tabla de fechas puede contener muchas representaciones diferentes de fechas y horas. Por ejemplo, una tabla de fechas suele tener columnas, como Año fiscal, Mes, Trimestre o Período, que puede seleccionar como campos de una lista de campos al segmentar y filtrar los datos en tablas dinámicas o informes de Power View.
Lista de campos de Power View
Para que las columnas de fechas, como Año, Mes y Trimestre, incluyan todas las fechas de su intervalo respectivo, la tabla de fechas debe tener al menos una columna con un conjunto de fechas contiguas. Es decir, esa columna debe tener una fila para cada día de cada año incluido en la tabla de fechas.
Por ejemplo, si los datos que desea examinar tienen fechas del 1 de febrero de 2010 al 30 de noviembre de 2012 e informa sobre un año natural, querrá una tabla de fechas con al menos un intervalo de fechas del 1 de enero de 2010 al 31 de diciembre de 2012. En la tabla de fechas, cada año debe contener todos los días de cada año. Si va a actualizar periódicamente los datos con datos más recientes, es posible que desee retrasar la fecha de finalización uno o dos años, para no tener que actualizar la tabla de fechas a medida que pasa el tiempo.
Tabla de fechas con un conjunto de fechas contiguas
Si informa sobre un año fiscal, puede crear una tabla de fechas con un conjunto de fechas contiguas para cada año fiscal. Por ejemplo, si el año fiscal comienza el 1 de marzo y tiene datos para los años fiscales 2010 hasta la fecha actual (por ejemplo, en el año fiscal 2013), puede crear una tabla de fechas que comience el 1/03/2009 e incluya al menos todos los días de cada año fiscal hasta la última fecha del año fiscal 2013.
Si va a informar sobre el año natural y el año fiscal, no es necesario crear tablas de fechas independientes. Una tabla de fechas única puede incluir columnas para un año natural, un año fiscal e incluso un calendario de período de trece cuatro semanas. Lo importante es que la tabla de fechas contiene un conjunto contiguo de fechas para todos los años incluidos.
Agregar una tabla de fechas al modelo de datos
Hay varias maneras de agregar una tabla de fechas a su modelo de datos:
- Importar desde una base de datos relacional u otro origen de datos.
- Cree una tabla de fechas en Excel y, a continuación, copie o vincule a una nueva tabla en Power Pivot.
- Importación desde Microsoft Azure Marketplace.
Veamos cada uno de estos más de cerca.
Importar desde una base de datos relacional
Si importa algunos o todos los datos desde un almacenamiento de datos u otro tipo de base de datos relacional, es probable que ya haya una tabla de fechas y relaciones entre ella y el resto de los datos que está importando. Las fechas y el formato probablemente coincidan con las fechas de los datos de hechos y es probable que las fechas comiencen en el pasado y se remonten al futuro. La tabla de fechas que desea importar puede ser muy grande y contener un intervalo de fechas más allá de lo que necesitará incluir en el modelo de datos. Puede usar las características de filtro avanzadas del Asistente para importar tablas de Power Pivot para elegir de forma selectiva solo las fechas y las columnas concretas que realmente necesita. Esto puede reducir considerablemente el tamaño del libro y mejorar su rendimiento.
Asistente para la importación de tablas
En la mayoría de los casos, no será necesario crear columnas adicionales como Año fiscal, Semana, Nombre del mes, etc., ya que ya existirán en la tabla importada. Sin embargo, en algunos casos, después de importar la tabla de fechas en el modelo de datos, es posible que deba crear columnas de fechas adicionales, en función de una necesidad de informes concreta. Afortunadamente, esto es fácil de hacer con DAX. Obtendrá más información sobre cómo crear campos de tabla de fechas más adelante. Cada entorno es diferente. Si no está seguro de si sus orígenes de datos tienen una tabla de calendario o fecha relacionada, póngase en contacto con el administrador de la base de datos.
Crear una tabla de fechas en Excel
Puede crear una tabla de fechas en Excel y, a continuación, copiarla en una nueva tabla en el modelo de datos. Esto es realmente bastante fácil de hacer y te da mucha flexibilidad.
Al crear una tabla de fechas en Excel, comienza con una sola columna con un intervalo de fechas contiguas. Después, puede crear columnas adicionales, como Año, Trimestre, Mes, Año fiscal, Período, etc., en la hoja de cálculo de Excel con fórmulas de Excel, o bien, después de copiar la tabla en el modelo de datos, puede crearlas como columnas calculadas. La creación de columnas de fecha adicionales en Power Pivot se describe en la sección Agregar nuevas columnas de fecha a la tabla de fechas más adelante en este artículo.
Procedimiento: Crear una tabla de fechas en Excel y copiarla en el modelo de datos
En Excel, en una hoja de cálculo en blanco, en la celda A1, escriba un nombre de encabezado de columna para identificar un intervalo de fechas. Normalmente, será algo así como Date, DateTime o DateKey.
En la celda A2, escriba una fecha de inicio. Por ejemplo, 1/1/2010.
Haga clic en el controlador de relleno y arrástrelo hacia abajo hasta un número de fila que incluya una fecha de finalización. Por ejemplo, 31/12/2016.
Seleccione todas las filas de la columna Fecha (incluido el nombre del encabezado de la celda A1).
En el grupo Estilos , haga clic en Dar formato como tabla y seleccione un estilo.
En el cuadro de diálogo Dar formato como tabla , haga clic en Aceptar.
Copie todas las filas, incluido el encabezado.
En Power Pivot, en la pestaña Inicio , haga clic en Pegar.
En Pegar el nombre de la tabla vista previa>, escriba un nombre como Fecha o Calendar. Deje activada la opción Usar primera fila como encabezados de columnay haga clic en Aceptar.
La nueva tabla de fechas (denominada Calendar en este ejemplo) en Power Pivot tiene el siguiente aspecto:
Nota
También puede crear una tabla vinculada mediante Agregar al modelo de datos. Pero esto hace que el libro sea innecesariamente grande, ya que tiene dos versiones de la tabla de fechas; uno en Excel y otro en Power Pivot.
Nota
La fecha del nombre es una palabra clave en Power Pivot. Si asigna un nombre a la tabla que cree en Power Pivot Date, deberá escribir el nombre de la tabla entre comillas simples en cualquier fórmula de DAX que haga referencia a ella en un argumento. Todas las imágenes y fórmulas de ejemplo de este artículo hacen referencia a una tabla de fechas creada en Power Pivot denominada Calendar.
Ahora tiene una tabla de fechas en su modelo de datos. Puede agregar nuevas columnas de fecha, como Año, Mes, etc., mediante DAX.
Agregar nuevas columnas de fecha a la tabla de fechas
Una tabla de fechas con una sola columna de fechas que tenga una fila para cada día de cada año es importante para definir todas las fechas de un intervalo de fechas. También es necesaria para crear una relación entre la tabla de hechos y la tabla de fechas. Pero esa única columna de fecha con una fila para cada día no es útil cuando se analiza por fechas en una tabla dinámica o un informe de Power View. Desea que la tabla de fechas incluya columnas que le ayuden a agregar los datos para un rango o grupo de fechas. Por ejemplo, puede que quiera sumar los importes de ventas por mes o trimestre, o puede crear una medida que calcule el crecimiento de un año a otro. En cada uno de estos casos, la tabla de fechas necesita columnas de año, mes o trimestre que le permitan agregar los datos de ese período.
Si ha importado la tabla de fechas desde un origen de datos relacional, es posible que ya incluya los distintos tipos de columnas de fechas que desea. En algunos casos, es posible que desee modificar algunas de esas columnas o crear columnas de fecha adicionales. Esto es especialmente cierto si crea su propia tabla de fechas en Excel y la copia en el modelo de datos. Afortunadamente, crear nuevas columnas de fecha en Power Pivot es bastante fácil con las funciones de fecha y hora en DAX.
Recomendación
Si aún no ha trabajado con DAX, un buen lugar para empezar a aprender es con Inicio rápido: aprenda los aspectos básicos de DAX en 30 minutos en Office.com.
Funciones de fecha y hora de DAX
Si alguna vez ha trabajado con las funciones de fecha y hora en fórmulas de Excel, es probable que esté familiarizado con las funciones de fecha y hora. Aunque estas funciones son similares a sus homólogos de Excel, existen algunas diferencias importantes:
- Las funciones de fecha y hora de DAX usan un tipo de datos datetime.
- Pueden tomar valores de una columna como argumento.
- Se pueden usar para devolver o manipular valores de fecha.
Estas funciones se usan a menudo al crear columnas de fecha personalizadas en una tabla de fechas, por lo que es importante comprenderlas. Usaremos varias de estas funciones para crear columnas para Year, Quarter, FiscalMonth, etc.
Nota
Las funciones de fecha y hora de DAX no son las mismas que las de Inteligencia de tiempo. Obtenga más información sobre la inteligencia de tiempo en Power Pivot en Excel.
DAX incluye las siguientes funciones de fecha y hora:
- FECHA
- FECHANUMERO
- DÍA SIGUIENTE
- FECHA.MES
- FIN.MES
- HOUR
- MINUTE
- MONTH
- AHORA
- SECOND
- HORA
- HORANUMERO
- HOY
- DIASEM
- NUM.DE.SEMANA
- YEAR
- FRAC.AÑO
También hay muchas otras funciones de DAX que puede usar en sus fórmulas. Por ejemplo, muchas de las fórmulas descritas aquí usan funciones matemáticas y trigonométricas como MOD y TRUNC, funciones lógicas como SI y funciones de texto como FORMATO Para obtener más información acerca de otras funciones de DAX, consulte la sección Recursos adicionales más adelante en este artículo.
Ejemplos de fórmulas para un año natural
En los ejemplos siguientes se describen las fórmulas usadas para crear columnas adicionales en una tabla de fechas denominada Calendar. Una columna, denominada Date, ya existe y contiene un intervalo contiguo de fechas del 1/1/2010 al 31/12/2016.
Año
=AÑO([fecha])
En esta fórmula, la función AÑO devuelve el año del valor de la columna Fecha . Dado que el valor de la columna Fecha es de tipo de datos datetime, la función AÑO sabe cómo devolver el año de dicho tipo.
Mes
=MES([fecha])
En esta fórmula, de forma muy parecida a la función AÑO, podemos usar simplemente la función MES para devolver un valor de mes de la columna Fecha.
Trimestre
=INT(([Mes]+2)/3)
En esta fórmula, usamos la función INT para devolver un valor de fecha como un entero. El argumento que especificamos para la función INT es el valor de la columna Mes, agregue 2 y luego divídalo por 3 para obtener nuestro trimestre, del 1 al 4.
Nombre del mes
=FORMATO([fecha],"mmmm")
En esta fórmula, para obtener el nombre del mes, usamos la función FORMATO para convertir un valor numérico de la columna Fecha en texto. Especificamos la columna Fecha como primer argumento, y luego el formato; Queremos que el nombre de nuestro mes muestre todos los caracteres, así que usamos "mmmm". Nuestro resultado tiene el siguiente aspecto:
Si queremos devolver el nombre del mes abreviado a tres letras, usaríamos "mmm" en el argumento de formato.
Día de la semana
=FORMATO([fecha],"ddd")
En esta fórmula, usamos la función FORMATO para obtener el nombre del día. Debido a que solo queremos un nombre de día abreviado, especificamos "ddd" en el argumento de formato.
Ejemplo de tabla dinámica
Cuando tenga campos para fechas como Año, Trimestre, Mes, etc., puede usarlos en una tabla dinámica o un informe. Por ejemplo, la siguiente imagen muestra el campo ImporteVentas de la tabla de hechos Ventas en VALORES, y Año y Trimestre de la tabla de dimensiones Calendar en FILAS. SalesAmount se agrega para el contexto de año y trimestre.
Ejemplos de fórmulas para un año fiscal
Año fiscal
=SI([Mes]<= 6,[Año],[Año]+1)
En este ejemplo, el año fiscal comienza el 1 de julio.
No hay ninguna función que pueda extraer un año fiscal de un valor de fecha porque las fechas de inicio y finalización de un año fiscal suelen ser diferentes de las de un año natural. Para obtener el año fiscal, primero usamos una función SI para probar si el valor de Mes es menor o igual que 6. En el segundo argumento, si el valor de Mes es menor o igual que 6, devuelve el valor de la columna Año. Si no es así, devuelve el valor de Año y suma 1.
Otra manera de especificar un valor de mes de fin de año fiscal es crear una medida que especifique simplemente el mes. Por ejemplo, FYE:=6. Después, puede hacer referencia al nombre de la medida en lugar del número de mes. Por ejemplo, =SI([Mes]<=[FYE],[Año],[Año]+1). Esto proporciona más flexibilidad al hacer referencia al mes de fin del año fiscal en varias fórmulas diferentes.
Mes fiscal
=SI([Mes]<= 6, 6+[Mes], [Mes]- 6)
En esta fórmula, especificamos si el valor de [Mes] es menor o igual que 6, luego tomamos 6 y sumamos el valor de Mes, de lo contrario, restamos 6 del valor de [Mes].
Trimestre fiscal
=INT(([FiscalMonth]+2)/3)
La fórmula que usamos para FiscalQuarter es muy similar a la de Quarter en nuestro año calendario. La única diferencia es que especificamos [FiscalMonth] en lugar de [Month].
Festivos o fechas especiales
Es posible que desee incluir una columna de fecha que indique que determinadas fechas son días festivos o alguna otra fecha especial. Por ejemplo, puede que quiera sumar los totales de ventas del día de Año Nuevo agregando un campo de días festivos a una tabla dinámica como segmentación de datos o un filtro. En otros casos, es posible que desee excluir esas fechas de otras columnas de fechas o en una medida.
Incluir días festivos o especiales es bastante sencillo. Puede crear una tabla en Excel que tenga las fechas que desea incluir. A continuación, puede copiar o usar Agregar al modelo de datos para agregarlo al modelo de datos como una tabla vinculada. En la mayoría de los casos, no es necesario crear una relación entre la tabla y la tabla Calendar. Cualquier fórmula que haga referencia a ella puede usar la función VALORBUSCAR para devolver valores.
A continuación se muestra un ejemplo de una tabla creada en Excel que incluye los días festivos que se agregarán a la tabla de fechas:
| Fecha | Días no laborables |
|---|---|
| 1/1/2010 | Año nuevo |
| 11/25/2010 | Acción de Gracias |
| 12/25/2010 | Navidad |
| 1/1/2011 | Año nuevo |
| 11/24/2011 | Acción de Gracias |
| 12/25/2011 | Navidad |
| 1/1/2012 | Año nuevo |
| 22/11/2012 | Acción de Gracias |
| 12/25/2012 | Navidad |
| 1/1/2013 | Año nuevo |
| 11/28/2013 | Acción de Gracias |
| 12/25/2013 | Navidad |
| 11/27/2014 | Acción de Gracias |
| 12/25/2014 | Navidad |
| 01/01/2014 | Año nuevo |
| 11/27/2014 | Acción de Gracias |
| 12/25/2014 | Navidad |
| 1/1/2015 | Año nuevo |
| 11/26/2014 | Acción de Gracias |
| 12/25/2015 | Navidad |
| 01/01/2016 | Año nuevo |
| 11/24/2016 | Acción de Gracias |
| 12/25/2016 | Navidad |
En la tabla de fechas, creamos una columna llamada Días festivos y usamos una fórmula como esta:
=VALORDEBÚSQUEDA(Festivos[Festivos],Festivos[fecha],Calendar[fecha])
Echemos un vistazo a esta fórmula con más cuidado.
Usamos la función VALORBUSCAR para obtener los valores de la columna Días festivos de la tabla Días festivos. En el primer argumento, especificamos la columna donde estará el valor de nuestro resultado. Especificamos la columna Días festivos en la tabla Días festivos porque ese es el valor que queremos que se devuelva.
=VALORDEBÚSQUEDA(Festivos[Festivos],Festivos[fecha],Calendar[fecha])
Luego especificamos el segundo argumento, la columna de búsqueda que tiene las fechas que queremos buscar. Especificamos la columna Fecha en la tabla Días festivos , así:
=VALORDEBÚSQUEDA(Festivos[Festivos],Festivos[fecha],Calendar[fecha])
Por último, especificamos la columna de nuestra tabla Calendar que tiene las fechas que queremos buscar en la tabla Holiday. Por supuesto, esta es la columna Fecha de la tabla Calendar.
=VALORDEBÚSQUEDA(Festivos[Festivos],Festivos[fecha],Calendar[fecha])
La columna Días festivos devolverá el nombre de cada fila que tenga un valor de fecha que coincida con una fecha de la tabla Días festivos.
Calendario personalizado: trece períodos de cuatro semanas
Algunas organizaciones, como el comercio minorista o el servicio de alimentos, a menudo informan sobre diferentes períodos, como trece períodos de cuatro semanas. Con un calendario de trece períodos de cuatro semanas, cada período es de 28 días; por lo tanto, cada período contiene cuatro lunes, cuatro martes, cuatro miércoles, etc. Cada período contiene la misma cantidad de días y, por lo general, los días festivos caerán dentro del mismo período cada año. Puedes elegir iniciar un período cualquier día de la semana. Al igual que ocurre con las fechas de un calendario o un año fiscal, puede usar DAX para crear columnas adicionales con fechas personalizadas.
En los ejemplos siguientes, el primer período completo comienza el primer domingo del año fiscal. En este caso, el año fiscal comienza el 1/7.
Semana
Este valor nos da el número de semana a partir de la primera semana completa del año fiscal. En este ejemplo, la primera semana completa empieza en domingo, por lo que la primera semana completa del primer año fiscal en la tabla Calendar comienza en realidad el 4/7/2010 y continúa hasta la última semana completa en la tabla Calendar. Aunque este valor en sí no es tan útil en el análisis, es necesario calcularlo para su uso en otras fórmulas de período de 28 días.
=INT([fecha]-40356)/7)
Echemos un vistazo a esta fórmula con más cuidado.
En primer lugar, creamos una fórmula que devuelve los valores de la columna Fecha como un entero, como sigue:
=INT([fecha])
Luego queremos buscar el primer domingo del primer año fiscal. Vemos que es el 4/7/2010.
Reste ahora 40356 (que es el entero para 27/06/2010, el último domingo del año fiscal anterior) de ese valor para obtener el número de días desde el inicio de los días en nuestra tabla Calendar, como sigue:
=INT([fecha]-40356)
A continuación, divida el resultado entre 7 (días de una semana), así:
=INT(([fecha]-40356)/7)
El resultado es el siguiente:
Período
El período de este calendario personalizado contiene 28 días y siempre comenzará en domingo. Esta columna devolverá el número del período que comienza con el primer domingo del primer año fiscal.
=INT(([Semana]+3)/4)
Echemos un vistazo a esta fórmula con más cuidado.
En primer lugar, creamos una fórmula que devuelve un valor de la columna Semana como un entero, como sigue:
= INT([Semana])
A continuación, agregue 3 a ese valor, así:
=INT([Semana]+3)
A continuación, divide el resultado entre 4, así:
=INT(([Semana]+3)/4)
El resultado es el siguiente:
Período Año fiscal
Este valor devuelve el año fiscal durante un período.
=INT(([Período]+12)/13)+2008
Echemos un vistazo a esta fórmula con más cuidado.
En primer lugar, creamos una fórmula que devuelve un valor de Período y suma 12:
=([Período]+12)
Dividimos el resultado por 13, porque hay trece períodos de 28 días en el año fiscal:
=(([Período]+12)/13)
Agregamos 2010, porque ese es el primer año en la tabla:
=(([Período]+12)/13)+2010
Finalmente, usamos la función INT para eliminar cualquier fracción del resultado y devolver un número entero, cuando se divide por 13, así:
= INT(([Período]+12)/13)+2010
El resultado es el siguiente:
Periodo en AñoFiscal
Este valor devuelve el número de período, del 1 al 13, a partir del primer período completo (que comienza el domingo) de cada año fiscal.
=SI(RESTO([Período],13), RESTO([Período],13),13)
Esta fórmula es un poco más compleja, por lo que la describiremos primero en un lenguaje que entendamos mejor. Esta fórmula establece que divida el valor de [Período] por 13 para obtener un número de período (1-13) en el año. Si ese número es 0, devolver 13.
En primer lugar, creamos una fórmula que devuelve el resto del valor de Período por 13. Podemos usar el MOD (funciones matemáticas y trigonométricas) así:
= MOD([Period],13)
Esto, en su mayor parte, nos da el resultado que deseamos, excepto cuando el valor de Período es 0 porque esas fechas no están dentro del primer año fiscal, como en los primeros cinco días de nuestra tabla de fechas de Calendar de ejemplo. Podemos encargarnos de esto con una función IF. En caso de que nuestro resultado sea 0, devolvemos 13, así:
= IF(MOD([Período],13),MOD([Período],13),13)
El resultado es el siguiente:
Ejemplo de tabla dinámica
En la imagen siguiente se muestra una tabla dinámica con el campo ImporteVentas de la tabla de hechos Ventas en VALORES, y los campos PeriodAñoFiscal y PeríodoEnAñoFiscal de la tabla de dimensiones de fecha de Calendar en FILAS. SalesAmount se agrega para el contexto por año fiscal y período de 28 días en el año fiscal.
Relaciones
Después de crear una tabla de fechas en el modelo de datos, para empezar a examinar los datos en tablas dinámicas e informes, y para agregar datos basados en las columnas de la tabla de dimensiones de fechas, debe crear una relación entre la tabla de hechos con los datos de transacciones y la tabla de fechas.
Dado que necesita crear una relación basada en fechas, querrá asegurarse de crear esa relación entre columnas cuyos valores sean del tipo de datos datetime (Fecha).
Para cada valor de fecha de la tabla de hechos, la columna de búsqueda relacionada de la tabla de fechas debe contener valores coincidentes. Por ejemplo, una fila (registro de transacción) de la tabla de hechos de ventas con un valor de 15/08/2012 a las 12:00 AM en la columna DateKey debe tener un valor correspondiente en la columna Fecha relacionada de la tabla de fecha (denominada Calendar). Esta es una de las razones más importantes por las que desea que la columna de fecha de la tabla de fechas contenga un intervalo de fechas contiguo que incluya cualquier fecha posible en la tabla de hechos.
Nota
Aunque la columna de fecha de cada tabla debe ser del mismo tipo de datos (Fecha), el formato de cada columna es indiferente.
Nota
Si Power Pivot no permite crear relaciones entre las dos tablas, es posible que los campos de fecha no almacenen la fecha y la hora con el mismo nivel de precisión. Según el formato de columna, los valores pueden tener el mismo aspecto, pero almacenarse de forma diferente. Lea más sobre cómo trabajar con el tiempo.
Nota
Evite usar claves suplentes enteras en las relaciones. Cuando se importan datos de un origen de datos relacional, a menudo las columnas de fecha y hora se representan mediante una clave suplente, que es una columna entera que se usa para representar una fecha única. En Power Pivot, debe evitar crear relaciones mediante claves de fecha y hora enteras y, en su lugar, usar columnas que contengan valores únicos con un tipo de datos de fecha. Aunque el uso de claves suplentes se considera una práctica recomendada en los almacenes de datos tradicionales, las claves enteras no son necesarias en Power Pivot y pueden dificultar la agrupación de valores en tablas dinámicas por diferentes períodos de fecha.
Si obtiene un error de coincidencia de tipos al intentar crear una relación, es probable que se deba a que la columna de la tabla de hechos no es del tipo de datos Fecha. Esto puede ocurrir cuando Power Pivot no puede convertir automáticamente un tipo de datos que no es fecha (normalmente, un tipo de datos de texto) en un tipo de datos de fecha. Aún puede usar la columna en la tabla de hechos, pero tendrá que convertir los datos con una fórmula DAX en una nueva columna calculada. Vea Convertir fechas de tipo de datos de texto en un tipo de datos de fecha más adelante en el apéndice.
Relaciones múltiples
En algunos casos, puede ser necesario crear varias relaciones o crear varias tablas de fechas. Por ejemplo, si hay varios campos de fecha en la tabla de hechos de ventas, como DateKey, ShipDate, y ReturnDate, todos pueden tener relaciones con el campo Fecha de la tabla de fechas de Calendar, pero solo uno de ellos puede ser una relación activa. En este caso, dado que DateKey representa la fecha de la transacción y, por lo tanto, la fecha más importante, sería mejor como relación activa . Los demás tienen relaciones inactivas.
En la siguiente tabla dinámica se calculan las ventas totales por año fiscal y trimestre fiscal. Una medida denominada Ventas totales, con la fórmula Ventas totales:=SUMA([ImporteVentas]), se coloca en VALORES, y los campos AñoFiscal y TrimestreFiscal de la tabla de fechas de Calendar se colocan en FILAS.
Esta tabla dinámica sencilla funciona correctamente porque queremos sumar nuestras ventas totales por la fecha de transacción en DateKey. Nuestra medida Ventas totales usa las fechas de DateKey y se suma por año fiscal y trimestre fiscal porque existe una relación entre DateKey en la tabla Ventas y la columna Fecha en la tabla de fechas de Calendar.
Relaciones inactivas
Pero, ¿qué pasaría si quisiéramos sumar nuestras ventas totales no por fecha de transacción, sino por fecha de envío? Necesitamos una relación entre la columna FechaDeEnvío de la tabla Sales y la columna Fecha de la tabla Calendar. Si no creamos esa relación, nuestras agregaciones siempre se basarán en la fecha de la transacción. Sin embargo, podemos tener varias relaciones, aunque solo una pueda estar activa, y como la fecha de transacción es la más importante, obtiene la relación activa con la tabla de Calendar.
En este caso, ShipDate tiene una relación inactiva, por lo que cualquier fórmula de medida creada para agregar datos basados en fechas de envío debe especificar la relación inactiva mediante la función USERELATIONSHIP .
Por ejemplo, dado que hay una relación inactiva entre la columna FechaDeEnvío de la tabla Ventas y la columna Fecha de la tabla Calendar, podemos crear una medida que sume las ventas totales por fecha de envío. Usamos una fórmula como esta para especificar la relación a usar:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Esta fórmula simplemente establece: Calcula una suma para ImporteVentas, pero filtra mediante la relación entre la columna FechaDeEnvío de la tabla Ventas y la columna Fecha de la tabla Calendar.
Ahora, si creamos una tabla dinámica y ponemos la medida Ventas totales por fecha de envío en VALORES, y Año fiscal y Trimestre fiscal en FILAS, vemos el mismo Total general, pero todos los demás importes de suma para el año fiscal y el trimestre fiscal son diferentes porque se basan en la fecha de envío y no en la fecha de transacción.
El uso de relaciones inactivas permite usar solo una tabla de fechas, pero requiere que cualquier medida (como Ventas totales por fecha de envío) haga referencia a la relación inactiva en su fórmula. Existe otra alternativa, es decir, utilizar varias tablas de fechas.
Varias tablas de fechas
Otra forma de trabajar con varias columnas de fechas en la tabla de hechos es crear varias tablas de fechas y crear relaciones activas separadas entre ellas. Veamos de nuevo nuestro ejemplo de tabla de ventas. Tenemos tres columnas con fechas en las que podríamos querer agregar datos:
- Una clave de fecha con la fecha de venta de cada transacción.
- A ShipDate: con la fecha y hora en que se enviaron los artículos vendidos al cliente.
- A ReturnDate: con la fecha y hora en que se recibieron uno o más elementos devueltos.
Recuerde que el campo DateKey con la fecha de la transacción es el más importante. Haremos la mayoría de nuestras agregaciones en función de estas fechas, por lo que seguramente querremos una relación entre esta y la columna Fecha de la tabla Calendar. Si no queremos crear relaciones inactivas entre FechaDeEnvío y FechaDevuelta y el campo Fecha de la tabla Calendar, lo que requiere fórmulas de medida especial, podemos crear tablas de fechas adicionales para la fecha de envío y la fecha de devolución. Entonces podemos crear relaciones activas entre ellos.
En este ejemplo, hemos creado otra tabla de fechas denominada ShipCalendar. Esto, por supuesto, también significa crear columnas de fecha adicionales, y dado que estas columnas de fecha están en una tabla de fechas diferente, queremos nombrarlas de forma que las diferencie de las mismas columnas de la tabla Calendar. Por ejemplo, hemos creado columnas denominadas AñoEnvío, MesEnvío, TrimestreDeEnvío, etc.
Si creamos nuestra tabla dinámica y ponemos nuestra medida de ventas totales en VALORES, y ShipFiscalYear y ShipFiscalQuarter en filas, vemos los mismos resultados que vimos cuando creamos una relación inactiva y un campo calculado Ventas totales especiales por fecha de envío.
Cada uno de estos enfoques requiere una consideración cuidadosa. Cuando se utilizan varias relaciones con una única tabla de fechas, puede que tenga que crear medidas especiales que transiten las relaciones inactivas mediante la función USERELATIONSHIP. Por otro lado, crear varias tablas de fechas puede resultar confuso en una lista de campos y, dado que tiene más tablas en el modelo de datos, requerirá más memoria. Experimente con lo que funciona mejor para usted.
Propiedad Tabla de fechas
La propiedad Tabla de fechas establece los metadatos necesarios para que Time-Intelligence funciones como TOTALYTD, PREVIOUSMONTH y DATESBETWEEN funcionen correctamente. Cuando se ejecuta un cálculo mediante una de estas funciones, el motor de fórmulas de Power Pivot sabe dónde ir para obtener las fechas que necesita.
Advertencia
Si no se establece esta propiedad, es posible que las medidas que usan funciones Time-Intelligence DAX no devuelvan resultados correctos.
Al establecer la propiedad Tabla de fechas, especifica una tabla de fechas y una columna de fecha del tipo de datos Fecha (fecha y hora) que contiene.
Procedimiento: establecer la propiedad de tabla de fechas
- En la ventana de PowerPivot, seleccione la tabla Calendario.
- En la pestaña Diseño , haga clic en Marcar como tabla de fechas.
- En el cuadro de diálogo Marcar como tabla de fechas, seleccione una columna con valores únicos y el tipo de datos Fecha.
Trabajar con el tiempo
Todos los valores de fecha con un tipo de datos Fecha en Excel o SQL Server son realmente un número. En ese número se incluyen dígitos que hacen referencia a una hora. En muchos casos, esa hora para todas y cada una de las filas es la medianoche. Por ejemplo, si un campo DateTimeKey de una tabla de hechos de ventas tiene valores como 19/10/2010 12:00:00 a.m., esto significa que los valores tienen el nivel de precisión del día. Si los valores del campo DateTimeKey tienen una hora incluida, por ejemplo, 19/10/2010 8:44:00 AM, esto significa que los valores tienen el nivel de precisión al minuto. Los valores también podrían tener la precisión del nivel de hora o incluso el nivel de precisión de segundos. El nivel de precisión en el valor de hora tendrá un impacto significativo en la forma de crear la tabla de fechas y en las relaciones entre esta y la tabla de hechos.
Debe determinar si va a agregar los datos a un nivel de día de precisión o a un nivel de tiempo de precisión. En otras palabras, es posible que quiera usar columnas en la tabla de fechas, como Mañana, Tarde u Hora, como campos de fecha y hora en las áreas fila, columna o filtro de una tabla dinámica.
Nota
Los días son la unidad de tiempo más pequeña con la que pueden trabajar las funciones de inteligencia de tiempo de DAX. Si no necesita trabajar con valores de tiempo, debe reducir la precisión de los datos para usar días como unidad mínima.
Si tiene la intención de agregar los datos al nivel de tiempo, la tabla de fechas necesitará una columna de fecha con la hora incluida. De hecho, necesitará una columna de fecha con una fila por cada hora, o tal vez incluso cada minuto, de cada día, para cada año en el intervalo de fechas. Esto se debe a que, para crear una relación entre la columna DateTimeKey de la tabla de hechos y la columna de fecha de la tabla de fechas, debe tener valores coincidentes. Como puedes imaginar, si incluyes muchos años, esto puede hacer que la tabla de fechas sea muy grande.
Sin embargo, en la mayoría de los casos, desea agregar los datos solo al día. En otras palabras, usará columnas como Año, Mes, Semana o Día de la semana como campos en las áreas de fila, columna o filtro de una tabla dinámica. En este caso, la columna de fecha de la tabla de fechas solo necesita contener una fila para cada día de un año, como hemos descrito anteriormente.
Si la columna de fecha incluye un nivel de tiempo de precisión, pero solo agregará a un nivel de día para crear la relación entre la tabla de hechos y la tabla de fechas, es posible que tenga que modificar la tabla de datos creando una nueva columna que trunca los valores de la columna de fecha a un valor de día. En otras palabras, convierta un valor como 19/10/2010 8:44:00AM en 19/10/2010 12:00:00 AM. A continuación, puede crear la relación entre esta nueva columna y la columna de fecha de la tabla de fechas, dado que los valores coinciden.
Veamos un ejemplo. Esta imagen muestra una columna DateTimeKey en la tabla de hechos de ventas. Todas las agregaciones de datos de esta tabla solo tienen que ser a nivel de día, mediante columnas en la tabla de fechas de Calendar como Año, Mes, Trimestre, etc. La hora incluida en el valor no es relevante, solo la fecha real.
Dado que no necesitamos analizar estos datos al nivel de tiempo, no necesitamos que la columna Fecha de la tabla de fechas de Calendar incluya una fila para cada hora y cada minuto de cada día de cada año. Por lo tanto, la columna Fecha de nuestra tabla de fechas tiene el siguiente aspecto:
Para crear una relación entre la columna DateTimeKey de la tabla Sales y la columna Date de la tabla Calendar, puede crear una nueva columna calculada en la tabla Sales fact y usar la función TRUNC para truncar el valor de fecha y hora de la columna DateTimeKey en un valor de fecha que coincida con los valores de la columna Fecha de la tabla Calendar. Nuestra fórmula es similar a la siguiente:
=TRUNCAR([DateTimeKey],0)
Esto nos da una nueva columna (hemos denominado DateKey) con la fecha de la columna DateTimeKey y una hora de 12:00:00 AM para cada fila:
Ahora podemos crear una relación entre esta nueva columna (DateKey) y la columna Fecha de la tabla Calendar.
De forma similar, podemos crear una columna calculada en la tabla Ventas que reduzca la precisión de tiempo en la columna DateTimeKey al nivel de precisión de horas. En este caso, la función TRUNC no funcionará, pero todavía podemos usar otras funciones de fecha y hora de DAX para extraer y reconcatenar un nuevo valor a un nivel de precisión de hora. Podemos usar una fórmula como esta:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Nuestra nueva columna tiene este aspecto:
Siempre que nuestra columna Fecha de la tabla de fechas tenga valores con el nivel de precisión de la hora, podemos crear una relación entre ellos.
Hacer que las fechas sean más utilizables
Muchas de las columnas de fechas que cree en la tabla de fechas son necesarias para otros campos, pero realmente no son tan útiles en el análisis. Por ejemplo, el campo Clave de fecha de la tabla Ventas a la que hemos hecho referencia y que hemos mostrado a lo largo de este artículo es importante porque, para cada transacción, dicha transacción se registra como si se produjera en una fecha y hora determinadas. Pero desde el punto de vista del análisis y la generación de informes, no es tan útil porque no podemos usarlo como una fila, columna o campo de filtro en una tabla dinámica o un informe.
Del mismo modo, en nuestro ejemplo, la columna Fecha de la tabla Calendar es muy útil, de hecho es fundamental, pero no puede usarla como una dimensión en una tabla dinámica.
Para que las tablas y sus columnas sean lo más útiles posible, y para facilitar la navegación por las listas de campos de informes de Power View o de tabla dinámica, es importante ocultar las columnas innecesarias de las herramientas de cliente. Es posible que también desee ocultar ciertas tablas. La tabla Días festivos mostrada anteriormente contiene fechas de días festivos que son importantes para determinadas columnas de la tabla Calendar, pero no puede usar las columnas Fecha y Días festivos de la propia tabla Días festivos como campos en una tabla dinámica. Una vez más, para facilitar la navegación por las listas de campos, puede ocultar toda la tabla de días festivos.
Otro aspecto importante de trabajar con fechas son las convenciones de nomenclatura. Puede asignar un nombre a las tablas y columnas de Power Pivot como desee. Pero tenga en cuenta que, especialmente si va a compartir el libro con otros usuarios, una buena convención de nomenclatura facilita la identificación de tablas y fechas, no solo en listas de campos, sino también en Power Pivot y en fórmulas de DAX.
Después de tener una tabla de fechas en el modelo de datos, puede empezar a crear medidas que le ayudarán a sacar el máximo partido de los datos. Algunas pueden ser tan sencillas como sumar los totales de ventas del año en curso y otras pueden ser más complejas, en las que es necesario filtrar por un intervalo concreto de fechas únicas. Obtenga más información en Medidas en Funciones de Power Pivot e inteligencia de tiempo.
Apéndice
Convertir el tipo de datos de texto en fecha en un tipo de datos de fecha
En algunos casos, una tabla de hechos con datos de transacción puede contener fechas de tipo de datos de texto. Es decir, una fecha que aparece como 2012-12-04T11:47:09 no es de hecho una fecha en absoluto, o al menos no es el tipo de fecha que Power Pivot puede entender. En realidad, es solo texto que se lee como una fecha. Para crear una relación entre una columna de fecha de la tabla de hechos y una columna de fecha de una tabla de fechas, ambas columnas deben ser del tipo de datos Fecha .
Normalmente, cuando intenta cambiar el tipo de datos de una columna de fechas que son de tipo de datos de texto a un tipo de datos de fecha, Power Pivot puede interpretar las fechas y convertirlas automáticamente en un tipo de datos de fecha real. Si Power Pivot no puede realizar una conversión de tipo de datos, recibirá un error de no coincidencia de tipos.
Sin embargo, todavía puede convertir las fechas en un tipo de datos de fecha real. Puede crear una nueva columna calculada y usar una fórmula DAX para analizar el año, mes, día, hora, etc. de las cadenas de texto y, a continuación, concatenarlos de forma que Power Pivot pueda leer como una fecha real.
En este ejemplo, hemos importado una tabla de hechos denominada Ventas a Power Pivot. Contiene una columna denominada DateTime. Los valores tienen este aspecto:
Si observamos el tipo de datos en la pestaña Inicio del grupo de formato de Power Pivot, vemos que es el tipo de datos de texto.
No podemos crear una relación entre la columna DateTime y la columna Date en nuestra tabla de fechas porque los tipos de datos no coinciden. Si intentamos cambiar el tipo de datos a Fecha, obtenemos un error de no coincidencia de tipos:
En este caso, Power Pivot no pudo convertir el tipo de datos de texto a fecha. Todavía podemos usar esta columna, pero para convertirla en un tipo de datos de fecha real, necesitamos crear una columna que analice el texto y lo vuelva a crear en un valor que Power Pivot puede convertir en un tipo de datos Fecha.
Recuerde, de la sección Trabajar con el tiempo anteriormente en este artículo; A menos que sea necesario que el análisis tenga una precisión de hora del día, debe convertir las fechas de la tabla de hechos a un nivel de precisión de un día. Teniendo esto en cuenta, queremos que los valores de nuestra nueva columna estén en el nivel de precisión del día (sin hora). Podemos convertir los valores de la columna DateTime en un tipo de datos de fecha y quitar el nivel de hora de precisión con la fórmula siguiente:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
Esto nos da una nueva columna (en este caso, denominada Fecha). Power Pivot incluso detecta que los valores son fechas y establece el tipo de datos automáticamente en Fecha.
Si queremos conservar el nivel de tiempo de precisión, simplemente extendemos la fórmula para incluir las horas, los minutos y los segundos.
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
Ahora que tenemos una columna Fecha del tipo de datos Fecha, podemos crear una relación entre ella y una columna de fecha en una fecha.
Recursos adicionales
Inicio rápido: Aprender los conceptos básicos de DAX en 30 minutos