Daño Promedio Móvil

SQL Server Denali PowerPivot Alberto Ferrari ya escribió sobre calcular promedios móviles en DAX usando una columna calculada. Me gustaría presentar un enfoque diferente aquí usando una medida calculada. Para el promedio móvil que calculo una media móvil diaria (durante los últimos 30 días) aquí. Para mi ejemplo, estoy utilizando el libro PowerPivot que se puede descargar como parte de los Proyectos de Modelo Tabular SSAS de las muestras de CTP 3 de Denali. En este post, estoy desarrollando la fórmula paso a paso. Sin embargo, si usted está en una prisa, usted puede directamente quiere saltar a los resultados finales a continuación. Con el año de calendario 2003 en el filtro, la fecha en las columnas y la cantidad de ventas (de la tabla Ventas por Internet) en los detalles, los datos de ejemplo son los siguientes: En cada contexto de filas, la expresión 8216DateDate da el contexto actual, es decir, la fecha de esta fila . Pero a partir de una medida calculada no podemos referir a esta expresión (ya que no hay ninguna fila actual para la tabla de fechas), en su lugar tenemos que utilizar una expresión como LastDate (8216DateDate). Por lo tanto, con el fin de obtener los últimos treinta días podemos utilizar esta expresión Ahora podemos resumir nuestras ventas por Internet para cada uno de esos días mediante el uso de la función de resumen: Summarize (DatesInPeriod (8216DateDate, LastDate (8216DateDate), - 30, DAY) 8217DateDate 8220SalesAmountSum8221 Sum (8216Internet SalesSales Importe)) Y, por último, estaban utilizando la función DAX AverageX para calcular el promedio de los 30 valores: Ventas Cantidad (30d promedio): AverageX (Resumir (DatesInPeriod (8216DateDate, LastDate (8216DateDate) ), SalesAmountSum) Este es el cálculo que estamos usando en nuestra tabla de ventas por Internet, como se muestra en la captura de pantalla de abajo: Al agregar este cálculo a la tabla de pivote de arriba, El resultado es el siguiente: Al mirar el resultado parece que no tenemos datos antes del 1 de enero de 2003: El primer valor de la media móvil es idéntico al valor del día (no hay filas antes de esa fecha). El segundo valor para la media móvil es en realidad el promedio de los dos primeros días y así sucesivamente. Esto no es muy correcto, pero estoy volviendo a este problema en un segundo. La captura de pantalla muestra el cálculo de la media móvil del 31 de enero como la media de los valores diarios del 2 al 31 de enero. Nuestra medida calculada también funciona bien cuando se aplican filtros. En la siguiente captura de pantalla usé dos categorías de productos para la serie de datos: ¿Cómo funciona nuestra medida calculada en niveles de agregación más altos? Para averiguar, Im usando la jerarquía de Calendario en las filas (en lugar de la fecha). Por razones de simplicidad, eliminé los niveles de semestre y trimestre usando las opciones de la tabla dinámica Excels (opción Mostrar / Ocultar campos). Como puede ver, el cálculo sigue funcionando bien. Aquí, el agregado mensual es el promedio móvil para el último día del mes específico. Puede ver esto claramente para enero (valor de 14,215.01 también aparece en la captura de pantalla anterior como el valor para el 31 de enero). Si este fue el requisito de negocio (que suena razonable para un promedio diario), entonces la agregación funciona bien en un nivel mensual (de lo contrario tendremos que afinar nuestro cálculo y este será un tema de la próxima publicación). Pero aunque la agregación tiene sentido en un nivel mensual, si ampliamos esta vista al nivel de día veremos que nuestra medida calculada simplemente devuelve la cantidad de ventas para ese día, no la media de los últimos 30 días más: ¿Cómo puede ser esto. El problema resulta del contexto en el que calculamos nuestra suma, tal y como se destaca en el siguiente código: Cantidad de ventas (30d promedio): PromedioX (Resumir (fechasenperíodo (8216DateDate, LastDate (8216DateDate), --30, DAY), 8217DateDate. 8220SalesAmountSum8221. Sum (8216Internet SalesSales Amount)), SalesAmountSum) Dado que evaluamos esta expresión durante el período de fechas dado, el único contexto que se sobrescribe aquí es 8216DateDate. En nuestra jerarquía se usaban diferentes atributos de nuestra dimensión (año civil, mes y día del mes). Como este contexto todavía está presente, el cálculo también es filtrado por esos atributos. Y esto explica por qué el contexto de los días actuales sigue presente para cada línea. Para hacer las cosas claras, siempre y cuando evaluemos esta expresión fuera de un contexto de fecha, todo está bien como muestra la siguiente consulta DAX al ser ejecutada por Management Studio en la perspectiva de ventas de Internet de nuestro modelo (utilizando la base de datos tabular con los mismos datos (8216DateDate, fecha (2003,1,1), - 5, DAY), 8217DateDate. 8220SalesAmountSum8221.Suma (8216Internet SalesSales Importe))) Aquí, he reducido el período de tiempo a 5 días y también establecer Una fecha fija como LastDate () resultaría en la última fecha de mi tabla de dimensión de fecha para la que no hay datos presentes en los datos de muestra. Aquí está el resultado de la consulta: Sin embargo, después de establecer un filtro a 2003, no se incluirán filas de datos fuera de 2003 en la suma. Esto explica la observación anterior: Parecía que sólo tenemos datos a partir del 1 de enero de 2003. Y ahora, sabemos por qué: El año 2003 fue en el filtro (como se puede ver en la primera pantalla de este post) y Por lo tanto estaba presente al calcular la suma. Ahora, todo lo que tenemos que hacer es deshacernos de esos filtros adicionales porque ya filtramos nuestros resultados por fecha. La forma más sencilla de hacerlo es utilizar la función Calculate y aplicar ALL () para todos los atributos para los que queremos eliminar el filtro. Como tenemos algunos de esos atributos (Año, Mes, Día, Día de la semana,) y queremos eliminar el filtro de todos ellos, pero el atributo de fecha, la función de acceso directo ALLEXCEPT es muy útil aquí. Si usted tiene un fondo MDX se preguntará por qué no tenemos un problema similar cuando se utiliza SSAS en modo OLAP (BISM Multidimensional). La razón es que nuestra base de datos OLAP tiene relaciones de atributo, por lo que después de configurar el atributo de fecha (clave), los otros atributos también se cambian automáticamente y no tenemos que cuidar de esto (ver mi mensaje aquí). Pero en el modelo tabular no tenemos relaciones de atributo (ni siquiera un verdadero atributo clave) y por lo tanto necesitamos eliminar los filtros no deseados de nuestros cálculos. Así que aquí estamos con el monto de las ventas (30d promedio): AverageX (Sumarize (datasinperiod (8216DateDate, LastDate (8216DateDate), - 30, DAY), 8217DateDate. 8220SalesAmountSum8221. Calcular (suma (8216Internet SalesSales Cantidad), ALLEXCEPT (8216Date8217,8217DateDate ) Esta es nuestra tabla pivote final en Excel: Para ilustrar el promedio móvil, aquí está el mismo extracto de datos en una vista de gráfico (Excel): Aunque filtrado nuestros datos en 2003 el promedio móvil para el primer 29 días de 2003 toma correctamente en cuenta los días correspondientes de 2002. Usted reconocerá los valores para el 30 y 31 de enero desde nuestro primer enfoque ya que éstos fueron los primeros días para los que nuestro primer cálculo tuvo una cantidad suficiente de datos (30 días completos). Servidor SQL Denali PowerPivot Alberto Ferrari ya escribió sobre el cálculo de las medias móviles en DAX utilizando una columna calculada. Me gustaría presentar aquí un enfoque diferente usando una medida calculada. Para la media móvil I8217m que calcula una media móvil diaria (durante los últimos 30 días) aquí. Para mi ejemplo, I8217m usando el libro PowerPivot que se puede descargar como parte de los Proyectos de Modelo Tabular SSAS de las muestras de Denali CTP 3. En este post, I8217m desarrollando la fórmula paso a paso. Sin embargo, si usted está en una prisa, usted puede directamente quiere saltar a los resultados finales a continuación. Con el año de calendario 2003 en el filtro, la fecha en las columnas y la cantidad de ventas (de la tabla Internet Sales) en los detalles, los datos de ejemplo se ven así: En cada contexto row8217s, la expresión DateDate da el contexto actual, es decir, la fecha de esta fila . Pero a partir de una medida calculada no podemos hacer referencia a esta expresión (ya que no hay ninguna fila actual para la tabla de fechas), en su lugar tenemos que utilizar una expresión como LastDate (DateDate). Por lo tanto, con el fin de obtener los últimos treinta días podemos utilizar esta expresión Ahora podemos resumir nuestras ventas por Internet para cada uno de esos días mediante el uso de la función de resumen: Resumir (160 DatesInPeriod (DateDate, LastDate (DateDate), - 30, DAY) 160, DateDate 160. quotSalesAmountSumquot 160. Sum (Internet SalesSales Cantidad)) Y finalmente, we8217re usando la función de DAX AverageX para calcular el promedio de los 30 valores: Ventas Cantidad (30d promedio): AverageX (160 Resumir (160160160 DatesInPeriod (DateDate, 160) 160, SalesAmountSum) Este es el cálculo que estamos utilizando en nuestra tabla de Ventas por Internet, como se muestra en la captura de pantalla que se muestra a continuación: Fecha de caducidad (FechaDate), - 30, DÍA) 160160160, FechaData 160160160. quotSalesAmountSumquot 160160160. Al agregar este cálculo a la tabla dinámica de arriba, el resultado se ve así: Mirando el resultado, parece que don8217t tiene datos antes del 1 de enero de 2003: El primer valor de la media móvil es idéntico al valor del día No hay filas antes de esa fecha). El segundo valor para la media móvil es en realidad el promedio de los dos primeros días y así sucesivamente. Esto no es del todo correcto, pero ahora vuelvo a este problema en un segundo. La captura de pantalla muestra el cálculo de la media móvil del 31 de enero como la media de los valores diarios del 2 al 31 de enero. Nuestra medida calculada también funciona bien cuando se aplican filtros. En la siguiente captura de pantalla usé dos categorías de productos para la serie de datos: ¿Cómo funciona nuestra medida calculada en niveles de agregación más altos? Para averiguar, I8217m usando la jerarquía de Calendario en las filas (en lugar de la fecha). Por simplicidad, eliminé los niveles de semestre y trimestre usando las opciones de tabla dinámica de Excel8217 (opción Mostrar / ocultar campos). Como puede ver, el cálculo sigue funcionando bien. Aquí, el agregado mensual es el promedio móvil para el último día del mes específico. Puede ver esto claramente para enero (valor de 14,215.01 también aparece en la captura de pantalla anterior como el valor para el 31 de enero). Si este fue el requisito de negocio (que suena razonable para un promedio diario), entonces la agregación funciona bien en un nivel mensual (de lo contrario tendremos que afinar nuestro cálculo y este será un tema de la próxima publicación). Pero aunque la agregación tiene sentido a un nivel mensual, si ampliamos esta vista al nivel de día, veremos que nuestra medida calculada simplemente devuelve la cantidad de ventas de ese día, no el promedio de los últimos 30 días: ¿Cómo puede ser esto? El resultado del problema es el contexto en el que calculamos nuestra suma, como se destaca en el siguiente código: Cantidad de ventas (30d promedio): PromedioX (160 Resumen (160160160 datasinperiod (DateDate, LastDate (DateDate), - 30, DAY) 160160160, DateDate 160160160. 160160160. quotSalesAmountSumquot Sum (Internet SalesSales Monto) 160) 160, SalesAmountSum) Desde evaluamos esta expresión durante el período de fechas determinado, el único contexto que se sobrescribe aquí, es DateDate. En nuestra jerarquía usamos diferentes atributos de nuestra dimensión (año civil, mes y día del mes). Como este contexto todavía está presente, el cálculo también es filtrado por esos atributos. Y esto explica por qué el contexto actual del día está todavía presente para cada línea. Para hacer las cosas claras, siempre y cuando evaluemos esta expresión fuera de un contexto de fecha, todo está bien como muestra la siguiente consulta DAX al ser ejecutada por Management Studio en la perspectiva de ventas de Internet de nuestro modelo (utilizando la base de datos tabular con los mismos datos ): evaluar (160160160 Resumir (160160160160160160160 datesinperiod (DateDate, fecha (2003,1,1), - 5, DIA) 160160160160160160160, DateDate 160160160160160160160. quotSalesAmountSumquot 160160160160160160160. Sum (Internet SalesSales Monto) 160 160 160)) Aquí, reduce el período de tiempo A 5 días y también establecer una fecha fija como LastDate (8230) daría lugar a la última fecha de mi tabla de dimensión de fecha para la que no hay datos presentes en los datos de muestra. Aquí está el resultado de la consulta: Sin embargo, después de establecer un filtro a 2003, no se incluirán filas de datos fuera de 2003 en la suma. Esto explica la observación anterior: Parecía que sólo tenemos datos a partir del 1 de enero de 2003. Y ahora, sabemos por qué: El año 2003 fue en el filtro (como se puede ver en la primera pantalla de este post) y Por lo tanto estaba presente al calcular la suma. Ahora, todo lo que tenemos que hacer es deshacernos de esos filtros adicionales porque we8217re ya filtramos nuestros resultados por Date. La forma más sencilla de hacerlo es usar la función Calculate y aplicar ALL (8230) para todos los atributos para los que queremos eliminar el filtro. Como tenemos algunos de esos atributos (año, mes, día, semana, 8230) y queremos eliminar el filtro de todos ellos, pero el atributo de fecha, la función de acceso directo ALLEXCEPT es muy útil aquí. Si usted tiene un fondo MDX se preguntará por qué don8217t obtener un problema similar cuando se utiliza SSAS en modo OLAP (BISM Multidimensional). La razón es que nuestra base de datos OLAP tiene relaciones de atributo, así que después de establecer el atributo de fecha (clave), los otros atributos también se cambian automáticamente y no tenemos que preocuparnos por esto (ver mi mensaje aquí). Pero en el modelo tabular no tenemos relaciones de atributo (ni siquiera un verdadero atributo de clave) y por lo tanto necesitamos eliminar los filtros no deseados de nuestros cálculos. Así que aquí estamos con la cantidad de ventas 8230 (30d avg): AverageX (160 Resumir (160160160 datesinperiod (DateDate, LastDate (DateDate), - 30, DIA) 160160160, 160160160. DateDate quotSalesAmountSumquot 160160160. calcular (Suma (Internet SalesSales Monto) , ALLEXCEPT (Fecha, DateDate)) 160), SalesAmountSum) Y esta es nuestra tabla dinámica final en Excel: Para ilustrar la media móvil, aquí es el mismo extracto de los datos en una vista de gráfico (Excel): a pesar de que filtramos nuestros datos sobre 2003 el promedio móvil para los primeros 29 días de 2003 toma correctamente en cuenta los días correspondientes de 2002. Usted reconocerá los valores para el 30 y 31 de enero de nuestro primer enfoque, ya que estos fueron los primeros días para los que nuestro primer cálculo tenía una cantidad suficiente de datos (30 días completos).Rolling 12 meses promedio en DAX Calculando el promedio móvil de 12 meses En DAX parece una tarea sencilla, pero esconde cierta complejidad. En este artículo se explica cómo escribir la mejor fórmula evitando las trampas comunes utilizando funciones de inteligencia de tiempo. Comenzamos con el habitual modelo de datos de AdventureWorks, con la tabla Productos, Ventas y Calendario. El calendario se ha marcado como una tabla de calendario (es necesario trabajar con cualquier función de inteligencia de tiempo) y hemos creado una jerarquía simple año-mes-fecha. Con esta configuración, es muy fácil crear una primera tabla dinámica que muestra las ventas a través del tiempo: Al hacer el análisis de tendencias, si las ventas están sujetas a la estacionalidad o, en términos más generales, si desea eliminar el efecto de los picos y caídas en las ventas, una La técnica común es la de calcular el valor durante un período dado, por lo general 12 meses, y la media. El promedio móvil durante 12 meses proporciona un indicador suave de la tendencia y es muy útil en los gráficos. Dada una fecha, podemos calcular el promedio móvil de 12 meses con esta fórmula, que todavía tiene algunos problemas que resolveremos más adelante: El comportamiento de la fórmula es simple: calcula el valor de Ventas después de crear un filtro en el calendario que Muestra exactamente un año completo de datos. El núcleo de la fórmula es el DATESBETWEEN, que devuelve un conjunto inclusivo de fechas entre los dos límites. La inferior es: Su lectura desde la más interna: si estamos mostrando datos de un mes, dicen julio de 2007, tomamos la última fecha visible mediante LastDate, que devuelve el último día de julio de 2007. Luego usamos nextDay a tomar la 1ª de agosto de 2007 y, finalmente, utilizamos SAMEPERIODLASTYEAR para cambiar de nuevo un año, obteniéndose 1 ª de agosto de 2006. el límite superior es simplemente LastDate, es decir, a finales de julio de 2007. Si utilizamos esta fórmula en una tabla dinámica, el resultado se ve bien, pero Tiene un problema para la última fecha: De hecho, como se puede ver en la figura, el valor se calcula correctamente hasta 2008. Entonces, no hay valor en 2009 (que es correcto, no tenemos ventas en 2009), pero no hay Un valor sorprendente en diciembre de 2010, donde nuestra fórmula muestra el total general en lugar de un valor en blanco, como cabría esperar. De hecho, en diciembre, LASTDATE devuelve el último día del año y NEXTDAY debe volver el 1 de enero de 2011. Pero NEXTDAY es una función de inteligencia de tiempo y se espera que devuelva conjuntos de fechas existentes. Este hecho no es muy evidente y vale la pena algunas palabras más. Las funciones de inteligencia de tiempo no realizan matemáticas en fechas. Si desea tomar el día después de una fecha determinada, simplemente puede agregar 1 a cualquier columna de fecha, y el resultado será el día siguiente. En su lugar, las funciones de inteligencia de tiempo cambian los conjuntos de fecha y vuelta en el tiempo. Así, NEXTDAY toma su entrada (en nuestro caso una tabla de una fila con el 31 de diciembre de 2010) y lo cambia un día después. El problema es que el resultado debe ser el 1 de enero de 2011 pero, como la tabla de calendario no contiene esa fecha, el resultado es BLANK. Por lo tanto, nuestra expresión calcula las ventas con un límite inferior en blanco, lo que significa el comienzo del tiempo, dando como resultado el gran total de las ventas. Para corregir la fórmula basta con cambiar el orden de evaluación del límite inferior: Como se puede ver, ahora se llama NEXTDAY después del cambio de un año atrás. De esta manera, tomamos 31 de diciembre de 2010, trasladarlo al 31 de diciembre de 2009 y tomar el día siguiente, que es el 1 de enero de 2010: una fecha existente en la tabla de calendario. El resultado es ahora el esperado: en este punto, solo necesitamos dividir ese número por 12 para obtener el promedio de balanceo. Pero, como se puede imaginar fácilmente, no siempre se puede dividir por 12. De hecho, al principio del período no hay 12 meses para agregar, pero un número menor. Tenemos que calcular el número de meses para los que hay ventas. Esto se puede lograr mediante el filtrado cruzado de la tabla de calendario con la tabla de ventas después de aplicar el nuevo contexto de 12 meses. Definimos una nueva medida que calcula el número de meses existentes en el período de 12 meses: Puede ver en la siguiente figura que la medida Months12M calcula un valor correcto: Vale la pena notar que la fórmula no funciona si elige un período Más de 12 meses, ya que CalendarMonthName tiene sólo 12 valores. Si necesita más tiempo, tendrá que utilizar una columna AAAAMM para poder contar más de 12. La parte interesante de esta fórmula que utiliza el filtrado de cruz es el hecho de que se calcula el número de meses disponibles incluso cuando filtra usando otra Atributos Si, por ejemplo, selecciona el color azul con un cortador, las ventas comienzan en julio de 2007 (no en 2005, como sucede con muchos otros colores). Usando el filtro cruzado en Ventas, la fórmula calcula correctamente que en julio de 2007 hay un mes de ventas disponibles para Blue: En este punto, el promedio de balanceo es sólo un DIVIDE de distancia: Cuando lo usamos en una Tabla de Pivot, todavía Tiene un pequeño problema: de hecho, el valor se calcula también para los meses para los que no hay ventas (es decir, meses futuros): Esto se puede resolver mediante una declaración de IF para evitar que la fórmula de mostrar los valores cuando no hay ventas. No tengo nada en contra de IF, pero, para los adictos al rendimiento entre ustedes, siempre vale la pena recordar que IF podría ser un asesino de rendimiento, ya que podría forzar el motor de la fórmula DAX a patear pulg En este caso específico, la diferencia es insignificante, pero , Como regla general, la mejor manera de quitar el valor cuando no hay ventas es confiar en fórmulas puras del motor del almacenaje como éste: Comparando un gráfico usando el Avg12M con otro que demuestre ventas usted puede apreciar fácilmente cómo la media móvil Describe las tendencias de una manera mucho más limpia: Descargar Manténgame informado sobre los próximos artículos (boletín). Desmarque para descargar libremente el archivo.