Las tablas de fechas de Power Pivot son esenciales para explorar y calcular datos a lo largo del tiempo. Este artículo proporciona una comprensión exhaustiva de las tablas de fechas y cómo puede crearlas en Power Pivot. En particular, este artículo describe:
-
Por qué una tabla de fechas es importante 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 fecha 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 nuevos de Power Pivot. Sin embargo, es importante tener una buena comprensión de la importación de datos, la creación de relaciones y la creación de columnas calculadas y medidas.
En este artículo no se describe cómo usar las funciones de Time-Intelligence de DAX en fórmulas de medida. Para obtener más información sobre cómo crear medidas con funciones de inteligencia de tiempo de DAX, consulte Inteligencia de tiempo en Power Pivot para Excel.
Nota: En Power Pivot, los nombres "medida" y "campo calculado" son sinónimos. Estamos usando la medida de nombres en este artículo. Para obtener más información, consulte Medidas en Power Pivot.
Contenido
Descripción de las tablas de fechas
Casi todos los análisis de datos conllevan la exploración y comparación de datos en fechas y horas. Por ejemplo, es posible que desee sumar los importes de ventas del último trimestre fiscal y, a continuación, comparar esos totales con otros trimestres, o puede que desee calcular un saldo final de cierre de mes para una cuenta. En cada uno de estos casos, está usando fechas como una forma de agrupar y agregar transacciones de ventas o saldos para un período determinado en el tiempo.
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 cortar y filtrar los datos en tablas dinámicas o informes de Power View.
Lista de campos de Power View
Para que las columnas de fecha como Año, Mes y Trimestre incluyan todas las fechas dentro de su intervalo respectivo, la tabla de fechas debe tener al menos una columna con un conjunto contiguo de fechas. Es decir, esa columna debe tener una fila para cada día de cada año incluida en la tabla de fechas.
Por ejemplo, si los datos que desea examinar tienen fechas entre el 1 de febrero de 2010 y el 30 de noviembre de 2012 y el informe de un año natural, querrá una tabla de fechas con al menos un intervalo de fechas entre el 1 de enero de 2010 y el 31 de diciembre de 2012. Cada año en la tabla de fechas debe contener todos los días de cada año. Si actualiza periódicamente los datos con datos más recientes, es posible que quiera ejecutar la fecha de finalización un año o dos, para que no tenga 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 contiguo de fechas para cada año fiscal. Por ejemplo, si el año fiscal comienza el 1 de marzo y tiene datos de ejercicios de 2010 actualizados hasta la fecha actual (por ejemplo, en AF 2013), puede crear una tabla de fechas que comience el 1/3/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 año natural y año fiscal, no es necesario crear tablas de fechas independientes. Una sola tabla de fechas puede incluir columnas para un año natural, un año fiscal e incluso trece calendarios de 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 al modelo de datos:
-
Importar desde una base de datos relacional u otro origen de datos.
-
Create una tabla de fechas en Excel y, a continuación, copie o vincule a una tabla nueva en Power Pivot.
-
Importar 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 de un almacén de datos u otro tipo de base de datos relacional, lo más probable es que ya haya una tabla de fechas y relaciones entre ella y el resto de datos que está importando. Es probable que las fechas y el formato coincidan con las fechas de los datos de hecho y que las fechas comiencen bien en el pasado y vayan mucho más allá en el futuro. La tabla de fechas que desea importar puede ser muy grande y contener un intervalo de fechas más allá de lo que deberá incluir en el modelo de datos. Puede usar las características de filtro avanzado del Asistente para la importación de tablas de Power Pivot para seleccionar de forma selectiva solo las fechas y las columnas concretas que realmente necesita. Esto puede reducir significativamente el tamaño del libro y mejorar el rendimiento.
Asistente para la importación de tablas
En la mayoría de los casos, no necesitará crear columnas adicionales como Año fiscal, Semana, Nombre del mes, etc. porque ya existen 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 fecha adicionales, en función de una determinada necesidad de creación de informes. 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 los orígenes de datos tienen una tabla de calendario o fecha relacionada, póngase en contacto con el administrador de la base de datos.
Create una tabla de fechas en Excel
Puede crear una tabla de fechas en Excel y después copiarla en una nueva tabla en el modelo de datos. Esto es muy fácil de hacer y le ofrece una gran flexibilidad.
Al crear una tabla de fechas en Excel, empieza con una sola columna con un intervalo de fechas contiguo. 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 usando fórmulas de Excel o, 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 que aparece más adelante en este artículo.
Cómo: Create 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 parecido a 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.
-
Seleccionar todas las filas de la columna Fecha (incluido el nombre del encabezado en la celda A1).
-
En el grupo Estilos , haga clic en Dar formato como tabla y, a continuación, 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 Vista previa de pegado > Nombre de tabla escriba un nombre como Fecha o Calendario. Deje activada la casilla Usar la primera fila como encabezadosde columna y, a continuación, haga clic en Aceptar.
La nueva tabla de fechas (denominada Calendario en este ejemplo) de Power Pivot tiene este aspecto:
Nota: También puede crear una tabla vinculada mediante Agregar al modelo de datos. Sin embargo, esto hace que el libro sea innecesariamente grande porque el libro tiene dos versiones de la tabla de fechas; una en Excel y otra en Power Pivot.
Nota: La fecha del nombre es una palabra clave en Power Pivot. Si asigna un nombre a la tabla que crea en Fecha de Power Pivot, tendrá que escribir el nombre de la tabla con 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 Calendario.
Ahora tiene una tabla de fechas en el 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 fecha 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 necesario para crear una relación entre la tabla de hechos y la tabla de fechas. Pero esa columna de fecha única con una fila para cada día no es útil al analizar por fechas en una tabla dinámica o un informe de Power View. Quiere que la tabla de fechas incluya columnas que le ayuden a agregar los datos de un intervalo o grupo de fechas. Por ejemplo, puede que desee sumar los importes de ventas por mes o trimestre, o bien puede crear una medida que calcule el crecimiento anual. En cada uno de estos casos, la tabla de fecha necesita columnas de año, mes o trimestre que le permitan agregar los datos para ese período.
Si importó la tabla de fechas desde un origen de datos relacional, es posible que ya incluya los distintos tipos de columnas de fecha que desee. 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 sencillo con funciones de fecha y hora en DAX.
Sugerencia: Si aún no ha trabajado con DAX, un excelente lugar para empezar a aprender es con Inicio rápido: Aprenda los conceptos básicos de DAX en 30 minutos en Office.com.
Funciones de fecha y hora de DAX
Si alguna vez ha trabajado con 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 equivalentes en Excel, hay 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 y/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 Año, Trimestre, Fin Fiscal, etc.
Nota: Las funciones de fecha y hora de DAX no son las mismas que las funciones de inteligencia de tiempo. Obtenga más información sobre inteligencia de tiempo en Power Pivot para Excel 2013.
DAX incluye las siguientes funciones de fecha y hora:
También puede usar muchas otras funciones de DAX en las 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 sobre otras funciones de DAX, vea 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 que se usan para crear columnas adicionales en una tabla de fechas denominada Calendario. Una columna, denominada Fecha, ya existe y contiene un intervalo contiguo de fechas entre el 1/1/2010 y el 31/12/2016.
Año
=AÑO([fecha])
En esta fórmula, la función AÑO devuelve el año a partir del valor de la columna Fecha. Dado que el valor de la columna Fecha es del tipo de datos datetime, la función AÑO sabe cómo devolver el año.
Mes
=MES([fecha])
En esta fórmula, al igual que con la función AÑO, podemos usar simplemente la función MES para devolver un valor de mes de la columna Fecha.
Trimestre
=ENTERO(([Mes]+2)/3)
En esta fórmula, usamos la función ENTERO para devolver un valor de fecha como un entero. El argumento que se especifica para la función ENTERO es el valor de la columna Mes, sumar 2 y dividirlo entre 3 para obtener nuestro trimestre, de 1 a 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. Se especifica la columna Fecha como primer argumento y, después, el formato; queremos que el nombre del mes muestre todos los caracteres, por lo que usamos "mmmm". Nuestro resultado es similar a este:
Si queremos que el nombre del mes se abrevia a tres letras, usaremos "mmm" en el argumento 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. Dado que solo queremos un nombre de día abreviado, especificamos "ddd" en el argumento formato.
Ejemplo de tabla dinámica
Una vez que tenga campos para fechas como Año, Trimestre, Mes, etc., puede usarlos en una tabla dinámica o un informe. Por ejemplo, en la imagen siguiente se muestra el campo SalesAmount de la tabla de hechos Ventas en VALORES y Año y Trimestre de la tabla de dimensiones Calendario en FILAS. SalesAmount se agrega para el contexto de año y trimestre.
Ejemplos de fórmula 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 a menudo son diferentes de las de un año natural. Para obtener el año fiscal, primero usamos una función SI para comprobar 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 sumar 1.
Otra forma de especificar un valor de mes final del año fiscal es crear una medida que simplemente especifique el mes. Por ejemplo, SI.A.CENT:=6. A continuación, puede hacer referencia al nombre de la medida en lugar del número de mes. Por ejemplo, =SI([Mes]<=[YE],[Año],[Año]+1). Esto proporciona más flexibilidad al hacer referencia al mes final 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, después tomamos 6 y agregamos el valor de Mes, de lo contrario restamos 6 del valor de [Mes].
Trimestre fiscal
=ENTERO(([FiscalMonth]+2)/3)
La fórmula que usamos para FiscalQuarter es muy parecida a la de Trimestre en nuestro año natural. La única diferencia es que especificamos [FiscalMonth] en lugar de [Month].
Días festivos o fechas especiales
Es posible que desee incluir una columna de fecha que indique que ciertas fechas son días festivos o alguna otra fecha especial. Por ejemplo, es posible que desee sumar los totales de ventas de Año Nuevo agregando un campo De vacaciones a una tabla dinámica, como una segmentación de datos o un filtro. En otros casos, es posible que desee excluir esas fechas de otras columnas de fecha 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. Después, 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 Calendario. Cualquier fórmula que haga referencia a ella puede usar la función BUSCARNUMERO 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 |
Nuevos años |
|
11/25/2010 |
Acción de gracias |
|
12/25/2010 |
Navidad |
|
1/1/2011 |
Nuevos años |
|
11/24/2011 |
Acción de gracias |
|
12/25/2011 |
Navidad |
|
1/1/2012 |
Nuevos años |
|
22/11/2012 |
Acción de gracias |
|
12/25/2012 |
Navidad |
|
1/1/2013 |
Nuevos años |
|
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 |
Nuevos años |
|
11/27/2014 |
Acción de gracias |
|
12/25/2014 |
Navidad |
|
1/1/2015 |
Nuevos años |
|
11/26/2014 |
Acción de gracias |
|
12/25/2015 |
Navidad |
|
01/01/2016 |
Nuevos años |
|
11/24/2016 |
Acción de gracias |
|
12/25/2016 |
Navidad |
En la tabla de fechas, creamos una columna denominada Días festivos y usamos una fórmula como esta:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Examinemos esta fórmula con más cuidado.
Usamos la función LOOKUPVALUE para obtener los valores de la columna Días festivos en la tabla Días festivos. En el primer argumento, especificamos la columna donde estará nuestro valor de resultado. Se especifica la columna Días festivos en la tabla Días festivos , ya que ese es el valor que queremos que se devuelva.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
A continuación, especificamos el segundo argumento, la columna de búsqueda que contiene las fechas que queremos buscar. Se especifica la columna Fecha en la tabla Días festivos , como esta:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
Por último, especificamos la columna de la tabla Calendario que contiene las fechas que queremos buscar en la tabla Vacaciones . Por supuesto, esta es la columna Fecha de la tabla Calendario .
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
La columna Días festivos devolverá el nombre de los días festivos 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 al por menor o el servicio de alimentos, suelen informar sobre períodos diferentes, 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 el mismo número de días y, normalmente, los días festivos se aplicarán al mismo período cada año. Puede elegir iniciar un período en cualquier día de la semana. Al igual que con las fechas de un calendario o 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 proporciona el número de semana que comienza con la primera semana completa del año fiscal. En este ejemplo, la primera semana completa comienza el domingo, por lo que la primera semana completa del primer año fiscal de la tabla Calendario comienza realmente el 4/7/2010 y continúa en la última semana completa de la tabla Calendario. Aunque este valor no es tan útil en el análisis, es necesario calcularlo para usarlo en otras fórmulas de período de 28 días.
=ENTERO([fecha]-40356)/7)
Examinemos 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 este:
=ENTERO([fecha])
Después, queremos buscar el primer domingo del primer año fiscal. Vemos que es 4/7/2010.
Ahora, reste 40356 (que es el entero del 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 Calendario, como este:
=ENTERO([fecha]-40356)
A continuación, divida el resultado entre 7 (días de una semana), como se muestra a continuación:
=ENTERO(([fecha]-40356)/7)
El resultado tiene este aspecto:
Período
El período de este calendario personalizado contiene 28 días y siempre comienza un domingo. Esta columna devolverá el número del período que comienza con el primer domingo del primer año fiscal.
=ENTERO(([Semana]+3)/4)
Examinemos 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 este:
=ENTERO([Semana])
A continuación, agregue 3 a ese valor, como este:
=ENTERO([Semana]+3)
A continuación, divide el resultado entre 4, de esta forma:
=ENTERO(([Semana]+3)/4)
El resultado tiene este aspecto:
Año fiscal de período
Este valor devuelve el año fiscal de un período.
=ENTERO(([Período]+12)/13)+2008
Examinemos esta fórmula con más cuidado.
En primer lugar, creamos una fórmula que devuelve un valor de Punto y suma 12:
= ([Punto]+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 es el primer año de la tabla:
=(([Período]+12)/13)+2010
Por último, usamos la función ENTERO para quitar cualquier fracción del resultado y devolver un número entero, cuando se divide por 13, como este:
=ENTERO(([Período]+12)/13)+2010
El resultado tiene este aspecto:
Período en Año Fiscal
Este valor devuelve el número de período, de 1 a 13, empezando por el primer período completo (que comienza el domingo) de cada año fiscal.
=SI(RESIDUO([Período];13); RESIDUO([Período];13);13)
Esta fórmula es un poco más compleja, por lo que la describiremos primero en un idioma que comprendamos mejor. Esta fórmula indica, divide el valor de [Período] entre 13 para obtener un número de período (1-13) en el año. Si ese número es 0, devuelve 13.
En primer lugar, creamos una fórmula que devuelve el resto del valor de Período por 13. Podemos usar la FUNCIÓN MOD (funciones matemáticas y trigonométricas) como esta:
=RESIDUO([Período],13)
Esto, en su mayor parte, nos da el resultado que queremos, excepto cuando el valor de Período es 0 porque esas fechas no pertenecen al primer año fiscal, como en los primeros cinco días de nuestra tabla de fechas calendario de ejemplo. Podemos ocuparnos de esto con una función SI. En caso de que nuestro resultado sea 0, devolveremos 13, así:
=SI(RESIDUO([Período];13);RESIDUO([Período];13);13)
El resultado tiene este aspecto:
Ejemplo de tabla dinámica
La imagen siguiente muestra una tabla dinámica con el campo SalesAmount de la tabla de hechos de ventas en VALUES y los campos PeriodFiscalYear y PeriodInFiscalYear de la tabla de dimensión de fecha Calendario en FILAS. SalesAmount se agrega para el contexto por año fiscal y período de 28 días en el ejercicio.
Relaciones
Después de crear una tabla de fechas en el modelo de datos, para empezar a explorar los datos en tablas dinámicas e informes y para agregar datos basados en las columnas de la tabla de dimensiones de fecha, 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 son del tipo de datos datetime (Date).
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 transacciones) en la tabla datos Ventas con un valor de 15/8/2012 12:00 AM en la columna DateKey debe tener un valor correspondiente en la columna Fecha relacionada de la tabla de fechas (denominada Calendario). 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 no importa.
Nota: Si Power Pivot no le permite crear relaciones entre las dos tablas, es posible que los campos de fecha no almacenen la fecha y la hora en el mismo nivel de precisión. Según el formato de columna, los valores pueden tener el mismo aspecto, pero almacenarse de forma diferente. Obtenga más información sobre cómo trabajar con el tiempo.
Nota: Evite usar claves subrogadas enteras en relaciones. Al importar datos de un origen de datos relacional, a menudo las columnas de fecha y hora se representan mediante una clave sustituta, que es una columna entera que se usa para representar una fecha única. En Power Pivot, debe evitar crear relaciones usando claves de fecha y hora enteras y, en su lugar, use columnas que contengan valores únicos con un tipo de datos de fecha. Aunque el uso de claves subrogadas 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 períodos de fecha diferentes.
Si recibe un error de coincidencia de tipo al intentar crear una relación, es probable que la columna de la tabla de hechos no sea del tipo de datos Fecha. Esto puede ocurrir cuando Power Pivot no puede convertir automáticamente una fecha no fecha (normalmente un tipo de datos de texto) en un tipo de datos de fecha. Todavía puede usar la columna en la tabla de hechos, pero tendrá que convertir los datos con una fórmula de DAX en una nueva columna calculada. Vea Convertir fechas de tipo de datos de texto a un tipo de datos de fecha más adelante en el apéndice.
Varias relaciones
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 fecha del calendario, 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 tanto, la fecha más importante, esto sería mejor que la relación activa . Los demás tienen relaciones inactivas.
La siguiente tabla dinámica calcula las ventas totales por año fiscal y trimestre fiscal. Una medida denominada Ventas totales, con la fórmula Ventas totales:=SUMA([SalesAmount]), se coloca en VALORES, y los campos Año Fiscal y Año Fiscal de la tabla de fechas Calendario se colocan en FILAS.
Esta tabla dinámica directa 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 Sales y la columna Date de la tabla De fecha del calendario.
Relaciones inactivas
Pero, ¿y si queríamos sumar nuestras ventas totales no por fecha de transacción, sino por fecha de envío? Necesitamos una relación entre la columna FechaDeEnlace de la tabla Ventas y la columna Fecha de la tabla Calendario. Si no creamos esa relación, nuestras agregaciones siempre se basan en la fecha de la transacción. Sin embargo, podemos tener varias relaciones, aunque solo una pueda estar activa, y dado que la fecha de la transacción es la más importante, obtiene la relación activa con la tabla Calendario.
En este caso, ShipDate tiene una relación inactiva, por lo que cualquier fórmula de medida creada para agregar datos en función de las fechas de envío debe especificar la relación inactiva mediante la función USERELATIONSHIP .
Por ejemplo, dado que existe una relación inactiva entre la columna FechaDeEnlace de la tabla Ventas y la columna Fecha de la tabla Calendario, 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 que se va a usar:
Ventas totales por fecha de envío:=CALCULATE(SUMA(Ventas[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
Esta fórmula simplemente indica: Calcular una suma para SalesAmount, pero filtrar mediante la relación entre la columna FechaDeEnlace de la tabla Ventas y la columna Fecha de la tabla Calendario.
Ahora, si creamos una tabla dinámica y colocamos la medida Total de ventas por fecha de envío en VALORES, y el año fiscal y el trimestre fiscal en FILAS, vemos el mismo total general, pero el resto de los 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.
Usar relaciones inactivas le 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. Hay otra alternativa, es decir, usar 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 independientes entre ellas. Veamos de nuevo nuestro ejemplo de tabla Ventas. Tenemos tres columnas con fechas en las que podríamos querer agregar datos:
-
Una DateKey con la fecha de venta de cada transacción.
-
Una FechaDeEnvío: con la fecha y hora en que los artículos vendidos se enviaron al cliente.
-
Un ReturnDate: con la fecha y hora en que se recibió uno o varios artículos devueltos.
Recuerde que el campo DateKey con la fecha de la transacción es más importante. Realizaremos la mayoría de las agregaciones en función de estas fechas, por lo que seguramente queremos una relación entre ella y la columna Fecha de la tabla Calendario. Si no queremos crear relaciones inactivas entre FechaDeEnvío y FechaDeEnvío y el campo Fecha de la tabla Calendario, por lo que necesitamos fórmulas de medida especial, podemos crear tablas de fecha adicionales para la fecha de envío y la fecha de devolución. A continuación, podemos crear relaciones activas entre ellas.
En este ejemplo, hemos creado otra tabla de fechas denominada ShipCalendar. Por supuesto, esto también significa crear columnas de fecha adicionales y, dado que estas columnas de fecha están en una tabla de fechas diferente, queremos asignarles un nombre de manera que las distinga de las mismas columnas de la tabla Calendario. Por ejemplo, hemos creado columnas denominadas AñoDeEntre, FindeEnlatro,Quarter, etc.
Si creamos nuestra tabla dinámica y colocamos nuestra medida Ventas totales en VALORES, y AñoDeEnvío y AñoDeEnvío en FILAS, vemos los mismos resultados que vimos cuando creamos una relación inactiva y un campo calculado especial Ventas totales por fecha de envío.
Cada uno de estos enfoques requiere una cuidadosa consideración. Al usar varias relaciones con una sola tabla de fechas, es posible que tenga que crear medidas especiales para el tránsito de 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, necesitará más memoria. Experimente con lo que funciona mejor para usted.
Propiedad Tabla de fechas
La propiedad Tabla de fecha establece los metadatos necesarios para que Time-Intelligence funciones como TOTALYTD, PREVIOUSMONTH y DATESBETWEEN funcionen correctamente. Cuando se ejecuta un cálculo con 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 usen funciones de Time-Intelligence DAX no devuelvan resultados correctos.
Al establecer la propiedad Tabla de fechas, se especifica una tabla de fechas y una columna de fecha del tipo de datos Fecha (datetime).
Cómo: Establecer la propiedad 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 fecha, seleccione una columna con valores únicos y el tipo de datos Fecha.
Trabajar con tiempo
Todos los valores de fecha con un tipo de datos Fecha en Excel o SQL Server son en realidad un número. En ese número se incluyen dígitos que hacen referencia a una hora. En muchos casos, ese tiempo para cada fila es medianoche. Por ejemplo, si un campo DateTimeKey de una tabla de hechos de ventas tiene valores como 19/10/2010 12:00:00, esto significa que los valores están en el nivel de precisión de 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 están en el nivel de precisión de minutos. Los valores también pueden ser la precisión de nivel de hora o incluso el nivel de segundos de precisión. El nivel de precisión en el valor de tiempo tendrá un impacto significativo en la forma de crear la tabla de fechas y las relaciones entre ella y la tabla de hechos.
Necesita determinar si agregará los datos a un nivel diario de precisión o a un nivel de precisión de tiempo. Es decir, es posible que desee usar columnas en la tabla de fechas como Mañana, Tarde o Hora como campos de fecha de 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 los días como unidad mínima.
Si su intención es agregar los datos al nivel de hora, 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 quizás 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 puede imaginar, si incluye muchos años, esto puede suponer una gran tabla de fechas.
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 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 del año, como hemos descrito anteriormente.
Si la columna de fecha incluye un nivel de precisión de hora, pero solo se 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 hechos 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 a 19/10/2010 12:00:00 AM. Después, puede crear la relación entre esta nueva columna y la columna de fecha de la tabla de fechas porque los valores coinciden.
Veamos un ejemplo. Esta imagen muestra una columna DateTimeKey en la tabla de hechos sales. Todas las agregaciones para los datos de esta tabla solo deben estar en el nivel de día, mediante el uso de columnas en la tabla de fechas calendario como Año, Mes, Trimestre, etc. La hora incluida en el valor no es relevante, solo la fecha real.
Dado que no es necesario analizar estos datos en el nivel de hora, no necesitamos la columna Fecha de la tabla de fechas del calendario para incluir una fila por hora y cada minuto de cada día en cada año. Por lo tanto, la columna Fecha de la tabla de fechas tiene este aspecto:
Para crear una relación entre la columna DateTimeKey de la tabla Sales y la columna Date de la tabla Calendario, podemos crear una nueva columna calculada en la tabla Datos de ventas y usar la función TRUNCAR 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 Calendario. Nuestra fórmula tiene este aspecto:
=TRUNCAR([DateTimeKey],0)
Esto nos proporciona una nueva columna (denominada 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 Date de la tabla Calendario.
De forma similar, podemos crear una columna calculada en la tabla Sales que reduzca la precisión de tiempo en la columna DateTimeKey al nivel de precisión de hora. En este caso, la función TRUNCAR no funcionará, pero todavía podemos usar otras funciones de fecha y hora de DAX para extraer y volver a concatenar un nuevo valor a un nivel de hora de precisión. Podemos usar una fórmula como esta:
= FECHA (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
Nuestra nueva columna tiene este aspecto:
Si nuestra columna Fecha de la tabla de fechas contiene valores para el nivel de precisión horaria, podemos crear una relación entre ellos.
Hacer que las fechas sean más utilizables
Muchas de las columnas de fecha 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 DateKey de la tabla Ventas a la que hemos hecho referencia y que se muestra a lo largo de este artículo es importante, ya que, para cada transacción, esa transacción se registra como que tiene lugar en una fecha y hora determinadas. Sin embargo, desde un punto de vista de análisis e informes, no es tan útil porque no podemos usarlo como campo de fila, columna o filtro en una tabla dinámica o informe.
De forma similar, en nuestro ejemplo, la columna Fecha de la tabla Calendario es muy útil, de hecho crítica, pero no puede usarla como dimensión en una tabla dinámica.
Para mantener las tablas y las columnas en ellas lo más útiles posible y facilitar la navegación por las listas de campos de informe de Power View o tabla dinámica, es importante ocultar las columnas innecesarias en las herramientas de cliente. Es posible que también quiera ocultar determinadas tablas. La tabla Días festivos que se muestra anteriormente contiene fechas de días festivos importantes para determinadas columnas de la tabla Calendario, pero no puede usar las columnas Fecha y Días festivos de la tabla Días festivos como campos en una tabla dinámica. Aquí de nuevo, para que Campo Listas más fácil de navegar, puede ocultar toda la tabla Holidays.
Otro aspecto importante del trabajo con fechas es la nomenclatura de convenciones. Puede asignar nombres a tablas y columnas en Power Pivot como desee. Pero tenga en cuenta, 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 campo, sino también en Power Pivot y en fórmulas 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. Algunos pueden ser tan sencillos como sumar los totales de ventas del año en curso, y otros pueden ser más complejos, donde es necesario filtrar por un intervalo determinado de fechas únicas. Obtenga más información en Medidas en Power Pivot y Funciones de inteligencia de tiempo.
Apéndice
Convertir fechas de tipo de datos de texto en un tipo de datos de fecha
En algunos casos, una tabla de hechos con datos de transacciones puede contener fechas de tipo de datos de texto. Es decir, una fecha que aparece como 2012-12-04T11:47:09 no es en realidad una fecha, o al menos no 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 en la tabla de hechos y una columna de fecha en 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 convertirla en un tipo de datos de fecha real automáticamente. Si Power Pivot no puede realizar una conversión de tipos de datos, recibirá un error de coincidencia de tipo.
Sin embargo, 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, concatenarla de nuevo para que Power Pivot pueda leerla como una fecha verdadera.
En este ejemplo, hemos importado una tabla de hechos denominada Ventas a Power Pivot. Contiene una columna denominada DateTime. Los valores aparecen así:
Si observamos Tipo de datos en la pestaña Inicio del grupo Formato de Power Pivot, vemos que es tipo de datos Texto.
No podemos crear una relación entre la columna DateTime y la columna Date de la tabla de fechas porque los tipos de datos no coinciden. Si intentamos cambiar el tipo de datos a Fecha, se produce un error de coincidencia de tipo:
En este caso, Power Pivot no pudo convertir el tipo de datos de texto a fecha. Podemos seguir usando esta columna, pero para introducirla en un tipo de datos de fecha real, necesitamos crear una nueva columna que analice el texto y lo vuelva a crear en un valor que Power Pivot puede hacer que sea un tipo de datos de fecha.
Recuerde, en la sección Trabajar con tiempo anteriormente en este artículo; A menos que sea necesario que el análisis tenga un nivel de precisión de hora del día, debe convertir las fechas de la tabla de hechos en un nivel de precisión de 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 (excluyendo la hora). Podemos convertir los valores de la columna DateTime en un tipo de datos de fecha y quitar el nivel de precisión de hora con la siguiente fórmula:
=FECHA(IZQUIERDA([DateTime],4), EXTRAE([DateTime],6,2), MID([DateTime],9,2))
Esto nos proporciona una nueva columna (en este caso, denominada Fecha). Power Pivot detecta incluso los valores que deben ser fechas y establece automáticamente el tipo de datos en Fecha.
Si queremos conservar el nivel de tiempo de precisión, simplemente ampliamos la fórmula para incluir las horas, minutos y segundos.
=FECHA(IZQUIERDA([DateTime],4), EXTRAE([DateTime],6,2), MID([DateTime],9,2)) +
HORA(EXTRAE([DateTime],12,2), EXTRAE([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
