Rolling 12 Months Average en DAX Computing el promedio móvil de 12 meses en DAX parece una tarea sencilla, pero oculta algo de 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 mostrando las ventas a lo largo del tiempo: Al hacer el análisis de tendencias, si las ventas están sujetas a la estacionalidad o, de manera más general, si desea eliminar el efecto de picos y caídas en las ventas, 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 Sales 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. El más bajo es: Leerlo desde lo más interno: si estamos mostrando los datos de un mes, digamos julio de 2007, tomamos la última fecha visible usando LASTDATE, que devuelve el último día en julio de 2007. Luego usamos NEXTDAY para tomar el 1er De agosto de 2007 y finalmente utilizamos SAMEPERIODLASTYEAR para cambiarlo de nuevo un año, dando el 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 parece bien, pero nos 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 periodos más largos, tendrá que usar una columna AAAAMM para poder contar más de 12. La parte interesante de esta fórmula que utiliza el filtrado cruzado es el hecho de que calcula el número de meses disponibles incluso cuando se filtra utilizando otros 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. Desempeño del Portafolio En este escaparate, mostramos cómo un profesional financiero puede usar Microsoft Power BI para analizar el rendimiento del portafolio. Dado un conjunto de operaciones de muestra, él o ella sería capaz de realizar un seguimiento de la tasa de rendimiento, así como cómo sus asignaciones cambian con el tiempo. Además, los indicadores de impulso como el oscilador estocástico y la divergencia de convergencia de media móvil ayudan a los analistas técnicos a evaluar cuándo puede ser apropiado realizar operaciones adicionales para los valores en su cartera. Insights sobre el desempeño histórico, así como donde las existencias son tendencias será de beneficio para cualquier persona que buscan evaluar y gestionar sus oficios. Esta demo utiliza los precios históricos de mercado de Microsoft, Tableau y Qlik. Quiere convertirse en un socio de BI de Power Los partners certificados son una parte importante del equipo Encuentre nuevas oportunidades de negocio, establezca conexiones y comparta los talentos y experiencia de su empresa con los usuarios de Power BI de todo el mundo. Cálculo DAX avanzado: haciendo un promedio móvil agrupado en PowerPivot Tengo una excelente pregunta la semana pasada en la página de preguntas a mis respuestas que me trajo una nueva comprensión de DAX. Así que finalmente una nueva entrada de blog interesante (espero) en DAX. Let8217s decir que tengo un conjunto de ventas por semana de una marca específica: Ahora quiero tener el promedio de ventas por semana (slicable por marca) y compararlo con el promedio de los últimos 3 totales semanales. Preparo el pivottable: Ahora para el promedio móvil de las últimas 3 semanas vamos a hacer algún DAX interesante. En primer lugar, queremos utilizar el actual 8220Week No8221 como un valor base, debemos comprobar si nuestra fórmula tiene una semana en el contexto de la fila actual, de lo contrario no podemos obtener una media móvil durante una semana específica. Comprobamos el número de valores en la columna SalesWeek No usando: if (countrows (values (SalesWeek No)) 1 A continuación queremos obtener los valores de ventas de esta semana y las dos semanas anteriores para obtener un promedio agrupado. Obtenga un promedio agrupado en SalesWeek No de las Ventas utilizando la función AVERAGEX: AVERAGEX (VALUES (SalesWeek No), SalesSum de Ventas) Observe que utilizamos VALUES (SalesWeek No) en función para agrupar en los distintos valores de SalesWeek No. Esta función en Su propia voluntad sólo devolverá el promedio de la actual SalesWeek No, tenemos que reemplazar el contexto de filtro para devolver la última semana actual y la anterior 2. Para reemplazar el contexto actual con un conjunto más amplio de valores, podemos utilizar la función Calculate. La función devolverá una tabla de la semana actual más las dos anteriores: CALCULATE (ltgrouped función de AVG SalesWeek No lt VALORES (SalesWeek No) ampamp SalesWeek No gt VALORES (SalesWeek No) -3) Si se combinan los dos tenemos el DAX general Función: Esto dará la siguiente tabla dinámica: Lo más interesante de notar aquí es que VALUES (SalesWeek No) contiene dos valores diferentes en una sola función. Let8217s echar un vistazo a la función DAX de nuevo: La función roja volverá a la fila real y el contexto del filtro que estamos pulg Para ver lo que este fila y el contexto del filtro es todo sobre este enlace. La función de cálculo cambiará el contexto del primer argumento, en este caso usamos AVERAGEX en el primer argumento para agrupar en el número de SalesWeek distinto del contexto cambiado. La función azul devuelve 3 SalesWeek No para hacer el promedio sobre. Puede parecer confuso, pero nuevamente muestra las increíbles capacidades de las funciones CALCULATE y AVERAGEX. Me tomó algún tiempo para llegar a CALCULAR en este escenario en particular (gracias a mi colega Jeffrey que también hizo un blog en profundidad sobre este tema). Varias maneras de abordar esto. Aquí hay una pareja para usted. Ambos requieren una tabla de fechas completamente poblada, separada de la tabla de datos principal (hechos). 1. Calc el promedio durante los últimos 84 días (12 semanas completas) como esto. Donde AvgSalesAmt es un promedio simple de SalesAmount para sus transacciones. FILTER sólo captura el conjunto de fechas que se encuentran en esa ventana de 84 días desde la fecha MAX. 2. Calc el promedio durante las últimas 12 semanas basado en un número de secuencia de la semana en su tabla de la fecha. El número de secuencia de la semana comenzaría en 1 durante la primera semana de su tabla de fechas y continuaría en todos los años de la tabla (sin restablecer al final del año). La secuencia de la semana se puede lograr como esto en DAX. Nota: sería más eficiente obtener este cálculo previamente calculado a partir de una dimensión de fecha relacional o SQL. Independientemente, si necesita hacerlo en DAX, esto funciona. El DimDateCalendarYearWeek está en el formato quotyyyy-wwquot. Eso le dará una secuencia que comienza en 1. Entonces usted puede calcular trece 12 semanas con esto: Déjeme saber si eso ayuda. Brent Greenwood, MS, MCITP, CBIP // Por favor marque las respuestas correctas y los mensajes útiles // brentgreenwood. blogspot Editado por Brent Greenwood Editor Jueves 16 de mayo de 2017 14:18 Propuesto como respuesta por Elvis Long Equipo de contingentes de Microsoft, May 17, 2017 7:28 AM Marcado como respuesta por Elvis Long Equipo contingente de Microsoft, Moderador Lunes 27 de mayo de 2017 2:58 AM Jueves, 16 de mayo de 2017 14:18 Varias maneras de abordar esto. Aquí hay una pareja para usted. Ambos requieren una tabla de fechas completamente poblada, separada de la tabla de datos principal (hechos). 1. Calc el promedio durante los últimos 84 días (12 semanas completas) como esto. Donde AvgSalesAmt es un promedio simple de SalesAmount para sus transacciones. FILTER sólo captura el conjunto de fechas que se encuentran en esa ventana de 84 días desde la fecha MAX. 2. Calc el promedio durante las últimas 12 semanas basado en un número de secuencia de la semana en su tabla de la fecha. El número de secuencia de la semana comenzaría en 1 durante la primera semana de su tabla de fechas y continuaría en todos los años de la tabla (sin restablecer al final del año). La secuencia de la semana se puede lograr como esto en DAX. Nota: sería más eficiente obtener este cálculo previamente calculado a partir de una dimensión de fecha relacional o SQL. Independientemente, si necesita hacerlo en DAX, esto funciona. El DimDateCalendarYearWeek está en el formato quotyyyy-wwquot. Eso le dará una secuencia que comienza en 1. Entonces usted puede calcular trece 12 semanas con esto: Déjeme saber si eso ayuda. Brent Greenwood, MS, MCITP, CBIP // Por favor marque las respuestas correctas y los mensajes útiles // brentgreenwood. blogspot Editado por Brent Greenwood Editor Jueves 16 de mayo de 2017 14:18 Propuesto como respuesta por Elvis Long Equipo de contingentes de Microsoft, 17 de mayo de 2017 7:28 Marcado como respuesta por Elvis Long Equipo de contingentes de Microsoft, Moderador Lunes 27 de mayo de 2017 2:58 AM Jueves, 16 de mayo de 2017 14:18 PMMoving Promedios controlados por Slicer La media móvil de dos meses es bastante suave . Pero seis meses es Smoooooother. (Imagina a Barry White diciendo eso: Ohhh Yeaahhh Un semestre Moooving promedio. Smoooooth) Ya ha sido un par de semanas atrás (yikes), pero recientemente escribí un post sobre los promedios móviles simples en Power Pivot. Una de las preguntas, en los comentarios, fue cómo controlar la duración del promedio móvil dinámicamente: ¿Qué es eso. Oye, cuando dos personas piden algo, y uno de ellos despliega CAPITAL LETTERS en el esfuerzo, y luego pares dijo mayúscula con uno de mis Palabras favoritas 8220 (mejorar), bueno, estoy enganchado. Hoy hacemos Postre Primero Vamos a trabajar hacia atrás a partir del resultado, vamos a La Slicer Controla la Longitud del Período MA y el Título de la Carta. Ahora que es SMOOOOOTH Otro cortador desconectado Primero creé una tabla en Excel normal, y lo copié en el portapapeles: Pegado en Power Pivot: Resultando en esta tabla: Quería los azulejos de la rebanadora de los Meses para incluir la columna para clasificar con sensatez, Agregó una columna de calc: Entonces puedo poner la rebanadora en mi pivote, y ordena en mi orden deseada: La medida de la cosechadora Seleccionada MA Longitud MAX (8216MA LongitudNúmero de Meses) Ceder una situación como: Variable Moving Sum y Medida Promedio Ahora su tiempo Para hacer algo con esa medida de la cosechadora. Variable Moving Sum CALCULATE (Unidades vendidas, DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate), Selected MA Length. Month)) La sección resaltada es la única diferencia entre esta medida y la suma móvil original de mi artículo anterior. Anteriormente, esa parte era hardwired a 3, para darnos una media móvil de 3 meses. Variable Moving Average Variable Moving Sum / CALCULATE (DISTINCTCOUNT (CalendarYear Month), DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate), Selected MA Length, Month)) Nuevamente, las partes resaltadas son las únicas diferencias entre esta medida y la MA de 3 meses . Un error Si el usuario de este informe / panel selecciona una de las opciones de avance en la rebanadora, el mes actual NO va a ser contado, mientras que se cuenta en las opciones de la parte posterior. ¿Por qué no se cuenta para la fórmula de la suma móvil de Here's, y resaltaré la sección ofensiva: Variable Moving Sum CALCULATE (Unidades vendidas, DATESINPERIOD (CalendarDate, LASTDATE (CalendarDate), Selected MA Length. Desde el ÚLTIMO DÍA del mes actual, se incluye el mes actual. Pero cuando seguimos adelante. Bien, el mes actual no está incluido. Por lo tanto, necesitamos un IF que compruebe si la longitud de MA seleccionada es positiva, y si es así, cambia el LASTDATE a FIRSTDATE. La lectura de la carta La última cosa que hacer es hacer la lectura del título del gráfico: Seleccione el título del gráfico, escriba un en la barra de fórmulas y seleccione una celda (G6 en este caso) Las fórmulas anteriores de G6 se utilizan para construir el propio G6 (Click for Larger Versión) Uno de los ingenieros fundadores de Power Pivot durante sus 14 años de carrera en Microsoft y creador del primer servicio Power Pivot en nubes, Rob es una de las principales autoridades en inteligencia de negocios de autoservicio y tecnología de hoja de cálculo de próxima generación. Este post tiene 25 comentarios Jeff Lingen dice: ¿Es posible 8220go uno mejor8221 y ser capaz de tener la medida Variable Moving Sum de interés ser una selección de rebanadora, así Por ejemplo, si tengo medidas para las unidades vendidas, los casos vendidos y Paletas Vendidas, ¿puedo elegir cuál de esas tres medidas para graficar y calcular un promedio móvil para ABSOLUTAMENTE puede porque cortadoras desconectadas son PURE MAGIC. En serio, si usted está leyendo esto y todavía tienen que hacer un loco, loco científico nivel de mierda con desconectado slicers8230 RUN, don8217t caminar, a su libro más cercano y empezar a experimentar. Cambiará su visión de la herramienta y el mundo de los datos. Así que imagina una tabla de dos columnas desconectado de subtítulos e ID8217s. 8220Units, 8221 1, 8220Cases8221, 2, 8220Pallets8221, 3 Entonces una medida de la segadora que es el MAX (o MIN, lo que sea) de esa columna ID. Entonces una tercera medida que es un INTERRUPTOR en esa cosechadora: SWITCH (la medida de la cosechadora aquí, 1, unidades vendió, 2, los casos vendió, 3, paletas vendidas) Substituye entonces la medida del interruptor en dondequiera que las unidades vendidas aparecen en el poste arriba. Bam Esto es bastante similar a la clase por truco de trilladoras, BTW: Chris Gilbert dice:
No comments:
Post a Comment