Promedio móvil para los 6 meses anteriores/los 6 siguientes

Necesito ayuda para calcular la media móvil, incluidos los 6 meses anteriores y los 6 meses posteriores. Supongo que para encontrar la media móvil basada en los últimos 6 meses, el cálculo es algo similar a:

Promedio móvil = CALCULAR (
PROMEDIOX(
Órdenes, Órdenes[Sales]),
FECHASENPERIODO(Pedidos[Ship Date],
LASTDATE(Pedidos[Ship Date]),
-6,
MES)
)

¿Cómo actualizo la fórmula para obtener la media móvil de los últimos 6 meses y los próximos 6 meses? (En Tableau se considera un cálculo móvil, MicroStrategy es una función OLAP Promedio). ¡Gracias!

Datos:

Año	Mes	Ventas
2012	enero	125421.9
2012	febrero	97483.81
2012	marcha	103970.4
2012	abril	116290.7
2012	Mayo	162398.2
2012	junio	142223.1
2012	mes de julio	162353.6
2012	agosto	142585.7
2012	septiembre	202582.3
2012	octubre	181586.7
2012	noviembre	532586.6
2012	diciembre	284846
2011	enero	126987.9
2011	febrero	87950.17
2011	marcha	65068.93
2011	abril	75960.54
2011	Mayo	144368.8
2011	junio	120091.5
2011	mes de julio	121481
2011	agosto	117987.7
2011	septiembre	285433.4
2011	octubre	260563.5
2011	noviembre	326864
2011	diciembre	195801.1

El gráfico me ayudó mucho a saber lo que necesitarías. Bien, entonces lo que realmente querrás son tres medidas: una SUMA de 12 meses, una cuenta de 12 meses para dividir y la medida de división. La razón por la que tenemos un conteo de 12 meses es que no siempre dividiremos por 12, a veces dividiremos por menos cuando nos acerquemos al principio o al final del rango de fechas. También me di cuenta de que hacer una ventana de -6 Y +6 meses en realidad causa una ventana de 13 meses, ya que AMBOS incluyen el mes actual, por lo que depende de usted decidir si + o – debe ser 5 en lugar de 6, que devolverá una ventana móvil máxima de 12 meses.

Suma de 12 meses:

12M Sum = 
CALCULATE (	
SUM(  FactOrderProduct[Net Profit]),
	DATESBETWEEN ( 
		'Date Table'[Date], 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), -5, MONTH ), 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), 6, MONTH )
	) 
)

Cuenta de 12 meses

12M Count = CALCULATE (
    CALCULATE ( COUNTROWS ( VALUES ( 'Date Table'[Month  Year] ) ), FactOrderProduct ),
    DATESBETWEEN ( 
		'Date Table'[Date], 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), -5, MONTH ), 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), 6, MONTH )
	)
)

Promedio de 12 meses:

12M Average = DIVIDE([12M Sum], [12M Count], 0)

Se puede encontrar más información aquí, modifiqué la consulta que muestran los chicos de SQLBI en esta publicación. Puede probarlo sin DateTable, no estoy 100% seguro de que funcione sin él. Pero siempre es una buena práctica tener uno, ya que muchas funciones en DAX requieren fechas contiguas para funcionar correctamente.

Saludos,

Reid

Hola,

Feliz de ayudar. Entonces, si entiendo esto correctamente, está buscando una «ventana» de promedio móvil de 12 meses basada en la ÚLTIMA FECHA (Órdenes[Ship Date]) ¿correcto? Una ventana que está seis meses en el pasado a partir de esta fecha Y seis meses en el futuro. Si ese es el caso, la fórmula se vería como algo a continuación. Avísame si te funciona.

Moving Average =
CALCULATE (
    AVERAGE ( Order[Sales] ),
    DATESBETWEEN (
        'Date Table'[Date],
        DATEADD ( LASTDATE ( Order[ShipDate] ), -6, MONTH ),
        DATEADD ( LASTDATE ( Order[ShipDate] ), 6, MONTH )
    )
)

Hola @Reid_Havens

Gracias por tu ayuda. Para su información, tuve un error tipográfico y necesito aprovechar la fecha del pedido, no la fecha de envío. Además, no tengo una tabla de fechas, ¿necesito una? Cuando hago el cálculo esta es mi fórmula para la nueva medida:

Moving Average =
CALCULATE (	
AVERAGE ( Orders[Sales]),
	DATESBETWEEN ( 
		Orders[Order Date], 
		DATEADD ( LASTDATE ( Orders[Order Date] ), -6, MONTH ), 
		DATEADD ( LASTDATE ( Orders[Order Date] ), 6, MONTH )
	) 
)

Sin embargo, el valor parece muy bajo para la media móvil:

Prueba en Tableau y MicroStrategy Visual Insight/Desktop a continuación, donde los cálculos de tablas rápidas o las métricas derivadas tienen cuadros de fórmula para los cálculos.

Aquí está el conjunto de datos completo:

Año	Mes	Ventas
2010	enero	139730.5
2010	febrero	130534.1
2010	marcha	145615.7
2010	abril	130543
2010	Mayo	93938.81
2010	junio	128304.3
2010	mes de julio	135036.5
2010	agosto	195567.4
2010	septiembre	126080.9
2010	octubre	259289
2010	noviembre	212633.3
2010	diciembre	227059.5
2011	enero	115144.9
2011	febrero	97745.88
2011	marcha	59156.74
2011	abril	74669.67
2011	Mayo	146157
2011	junio	116518.6
2011	mes de julio	116243.5
2011	agosto	118415.9
2011	septiembre	311721.7
2011	octubre	245237.7
2011	noviembre	323051.3
2011	diciembre	220444.6
2012	enero	96276.63
2012	febrero	95216.72
2012	marcha	102573.8
2012	abril	129869.1
2012	Mayo	152760.3
2012	junio	145649.7
2012	mes de julio	158229
2012	agosto	150146.1
2012	septiembre	199804.2
2012	octubre	199429.5
2012	noviembre	520100.4
2012	diciembre	280675.8
2013	enero	215229.2
2013	febrero	149129
2013	marcha	171791
2013	abril	143738.8
2013	Mayo	218862.2
2013	junio	155990.5
2013	mes de julio	166914.9
2013	agosto	288185.1
2013	septiembre	267567.2
2013	octubre	378212
2013	noviembre	375129.2
2013	diciembre	321610.8

El gráfico me ayudó mucho a saber lo que necesitarías. Bien, entonces lo que realmente querrás son tres medidas: una SUMA de 12 meses, una cuenta de 12 meses para dividir y la medida de división. La razón por la que tenemos un conteo de 12 meses es que no siempre dividiremos por 12, a veces dividiremos por menos cuando nos acerquemos al principio o al final del rango de fechas. También me di cuenta de que hacer una ventana de -6 Y +6 meses en realidad causa una ventana de 13 meses, ya que AMBOS incluyen el mes actual, por lo que depende de usted decidir si + o – debe ser 5 en lugar de 6, que devolverá una ventana móvil máxima de 12 meses.

Suma de 12 meses:

12M Sum = 
CALCULATE (	
SUM(  FactOrderProduct[Net Profit]),
	DATESBETWEEN ( 
		'Date Table'[Date], 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), -5, MONTH ), 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), 6, MONTH )
	) 
)

Cuenta de 12 meses

12M Count = CALCULATE (
    CALCULATE ( COUNTROWS ( VALUES ( 'Date Table'[Month  Year] ) ), FactOrderProduct ),
    DATESBETWEEN ( 
		'Date Table'[Date], 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), -5, MONTH ), 
		DATEADD ( LASTDATE ( 'Date Table'[Date] ), 6, MONTH )
	)
)

Promedio de 12 meses:

12M Average = DIVIDE([12M Sum], [12M Count], 0)

Se puede encontrar más información aquí, modifiqué la consulta que muestran los chicos de SQLBI en esta publicación. Puede probarlo sin DateTable, no estoy 100% seguro de que funcione sin él. Pero siempre es una buena práctica tener uno, ya que muchas funciones en DAX requieren fechas contiguas para funcionar correctamente.

Saludos,

Reid

Hola @Reid_Havens,

Hice algunos deberes y para las funciones de inteligencia de tiempo, las tablas de atenuación de fecha son obligatorias. Así que creé una dimensión de fecha simple creando una nueva tabla -> Fecha = CALENDARIO (FECHA (2010,1,1), FECHA (2013,12,31)). También creó una columna para Mes -> Lun-Año = FORMATO (‘Fecha'[Date]»MMMM AAAA»)

En cuanto a la fórmula en sí para el promedio móvil, tuve problemas con DATEADD para la fecha de finalización futura. Solo calcularía para 30 días, por lo que eliminaría los valores de los meses que tenían 31 días, etc. Por lo tanto, solo usé DATEADD para la fecha de inicio y EODMONTH para la fecha de finalización. Aquí están las fórmulas finales:

Para el Denominador de la Media Móvil:

Recuento de meses = CALCULAR (CALCULAR (CUENTAS (VALORES (‘Fecha’)[Mon-Year])), Pedidos), FECHAS ENTRE (‘Fecha'[Date], Dateadd(FIRSTDATE(Pedidos[Order Date]), -6, MES), EOMONTH(FIRSTDATE(Órdenes[Order Date]), 6)))

Para la propia media móvil:

Promedio móvil = DIVIDIR (CALCULAR (SUMA (Pedidos[Sales]), FECHAS ENTRE(‘Fecha'[Date], Dateadd(FIRSTDATE(Pedidos[Order Date]), -6, MES), EOMONTH(FIRSTDATE(Órdenes[Order Date]), 6))), [Month Count])

Salida final a continuación. ¡¡Muchas gracias por tu ayuda!! Me tienes exactamente en el lugar correcto. ¡¡Gracias de nuevo!!

PD: espero que en el futuro Power BI haga que estas funciones sean tan intuitivas como MSTR y Tableau, en lugar de tener que crear tantos componentes para un solo cálculo. 🙂

Hola @smp0150

En la última versión de Power BI Desktop (mayo de 2017), crearon una medida rápida para la media móvil que es exactamente lo que estaba tratando de lograr.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-may-feature-summary/#quickMeasures

Entradas relacionadas

Deja un comentario Cancelar respuesta