Problema de SQL de variación de precios

[Syndicate_Admin]

He creado una consulta SQL que ejecuta correctamente una determinación de desviación de precio utilizando la siguiente lógica:

Desviación de precios (Precio medio año corriente - Precio medio año anterior) / Volumen actual

Esto calcula cada mes frente al mismo mes un año anterior y solo cuándo y la combinación artículo / cliente coincide. El problema con el código es que esto se reinicia cada año, ya que estoy usando la lógica de Año actual - GetDate(). ¿Hay alguna manera de cambiar este código para que pueda obtener un gráfico rolling, por ejemplo, Diciembre 2020 vs Diciembre 2019 junto con Enero 2021 vs Enero 2020?

WITH cur ([cur_item],[cur_cust],[cur_year],[cur_month],[cur_price],[cur_extprice],[cur_volume],[cur_avgprice],[pick_category])

AS

(
SELECT
	inv_item_mst.item AS [cur_item],
	custaddr_mst.name AS [cur_cust],
	YEAR(inv_hdr_mst.inv_date) as [cur_year],
	MONTH(inv_hdr_mst.inv_date) as [cur_month],
        SUM(inv_item_mst.price) AS [cur_price],
	SUM(inv_item_mst.price*inv_item_mst.qty_invoiced) AS [cur_extprice],
	SUM(inv_item_mst.qty_invoiced) AS [cur_volume],
	SUM(inv_item_mst.price*inv_item_mst.qty_invoiced)/NULLIF(SUM(inv_item_mst.qty_invoiced),0) AS [cur_avgprice],
	item_mst.Uf_PickCategory AS [pick_category]

FROM inv_item_mst

LEFT JOIN inv_hdr_mst
	ON inv_item_mst.inv_num=inv_hdr_mst.inv_num
LEFT JOIN custaddr_mst
    ON inv_hdr_mst.cust_num=custaddr_mst.cust_num AND inv_hdr_mst.cust_seq=custaddr_mst.cust_seq
LEFT JOIN item_mst
	ON inv_item_mst.item=item_mst.item

WHERE YEAR(inv_hdr_mst.inv_date)=YEAR(GETDATE()) AND (inv_item_mst.price*inv_item_mst.qty_invoiced)>0

GROUP BY inv_item_mst.item,
custaddr_mst.name,
item_mst.Uf_PickCategory,
YEAR(inv_hdr_mst.inv_date),
MONTH(inv_hdr_mst.inv_date)

)

,

prev ([prev_item],[prev_cust],[prev_year],[prev_month],[prev_price],[prev_extprice],[prev_volume],[prev_avgprice])

AS

(
SELECT
	inv_item_mst.item AS [prev_item],
	custaddr_mst.name AS [prev_cust],
	YEAR(inv_hdr_mst.inv_date) as [prev_year],
	MONTH(inv_hdr_mst.inv_date) as [prev_month],
        SUM(inv_item_mst.price) AS [prev_price],
	SUM(inv_item_mst.price*inv_item_mst.qty_invoiced) AS [prev_extprice],
	SUM(inv_item_mst.qty_invoiced) AS [prev_volume],
	SUM(inv_item_mst.price*inv_item_mst.qty_invoiced)/NULLIF(SUM(inv_item_mst.qty_invoiced),0) AS [prev_avgprice]

FROM inv_item_mst

LEFT JOIN inv_hdr_mst
	ON inv_item_mst.inv_num=inv_hdr_mst.inv_num
LEFT JOIN custaddr_mst
    ON inv_hdr_mst.cust_num=custaddr_mst.cust_num AND inv_hdr_mst.cust_seq=custaddr_mst.cust_seq

WHERE YEAR(inv_hdr_mst.inv_date)=YEAR(GETDATE())-1 AND (inv_item_mst.price*inv_item_mst.qty_invoiced)>0

GROUP BY inv_item_mst.item,
custaddr_mst.name,
YEAR(inv_hdr_mst.inv_date),
MONTH(inv_hdr_mst.inv_date)

)

SELECT
cur.cur_year,
cur.cur_month,
cur.cur_item,
cur.cur_cust,
cur.pick_category,
cur_avgprice,
prev_avgprice,
cur_volume,
CASE
WHEN prev.prev_volume IS NULL
THEN 0
ELSE (cur.cur_avgprice - prev.prev_avgprice)*cur.cur_volume
END AS [price_variance]

from cur

LEFT JOIN prev
	ON cur.cur_item=prev.prev_item AND cur.cur_cust=prev.prev_cust AND cur.cur_month=prev.prev_month

[Syndicate_Admin]

En resumen, sí. Necesita una tabla Fechas/Calendario. También debe realizar el cálculo en DAX como medida. No es necesario realizar esa consulta SQL: solo necesita proporcionar los datos sin procesar.

Proporcione datos de ejemplo en formato utilizable (no como una imagen) y muestre el resultado esperado.

[Syndicate_Admin]

Lo siento por la espera de la respuesta. La persona que lo solicitó originalmente lo volvió a levantar. Hay un archivo aquí con algunos datos de ejemplo.

https://drive.google.com/file/d/1EEpP1HkAQc5P0MkazTu7-J6raLocjEbl/view?usp=sharing

Puedo investigarlo más usando la lógica que mencionaste. Sin embargo, intenté calcular una columna True False que determina si un customers create date está entre las fechas seleccionadas. ¿Alguna idea de cómo hacer que esas últimas citas sean verdaderas y el resto falsas?

Supongo que me falta la sintaxis DAX correcta para recoger las fechas que filtre en la tabla de fechas para impulsar una cálculo en mi tabla de varianza actual.

También sí, las tablas son las mismas. Quieren filtrar dos períodos de tiempo esencialmente y luego ejecutar cálculos en cualquier momento en que el cliente y el artículo tengan una transacción dentro del mismo mes, razón por la cual estaba tratando de filtrar ambos y luego unirse a las tablas Varianza actual y Desviación anterior en el cliente y el artículo.