Forum Discussion
Price Variance SQL Issue
I built a SQL Query that runs a price variance calculation correctly using the following logic:
Price Variance = (Avg Price Current Year - Avg Price Previous Year) / Current Volume
This calculates each month vs the same month a year prior and only when and item / customer combination matches. The issue with the code is that this restarts every year as I am using Current Year = GetDate() logic. Is there a way to change this code so I can get a rolling chart e.g., December 2020 vs December 2019 along with January 2021 vs January 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
2 Replies
In short, yes. You need a Dates/Calendar table. You also need to do the calculation in DAX as a measure. No need for that SQL query - you only need to provide the raw data.
Please provide sample data in usable format (not as a picture) and show the expected outcome.
Sorry for the wait for the reply. The person who requested it originally brought it back up again. There is a file here with some sample data.
https://drive.google.com/file/d/1EEpP1HkAQc5P0MkazTu7-J6raLocjEbl/view?usp=sharing
I can look more to into that using the logic you mentioned. I tried to calculate a True False column though that determines if a Customers Create Date is between the dates I selected. Any idea how to make those last dates True and the rest False?
Guess I am missing the right DAX syntax to pick up the dates I filter in the Date Table to drive a calculatation in my Current Variance Table.
Also yes the tables are the same. They want to filter two periods of time essentially and then run calculations on any time the customer and item have a transaction within the same month which is why I was trying to filter both and then join the Current Variance and Previous variance tables on customer and item.
