[Urgent help request] Monthly weighted average price this year vs last year

Anonymous — Mon, 27 Feb 2023 15:34:26 GMT

Hi Power BI experts,

I am calculating the inflation impact based on this calculation logic.

(Monthly weighted average price in this year's month - Monthly weighted average price in previous year's month) * Quantity in this year's month *(-1)

The basic logic (Pattern A) is done as per the below, but I realized that there are exceptions since we don't have transactions every single month, such as patterns B and C.

Could someone help me with building dynamic DAX logic with this sample data? Big thank you in advance!

Sample data and detailed explanation:

https://docs.google.com/spreadsheets/d/1CIhuSIddVvPKPDFy7m3E98AE4pSeU9WE/edit?usp=share_link&ouid=107681513386535148350&rtpof=true&sd=true

	This year's month	Last year's month	Action
Pattern A	Yes	Yes	Include
Pattern B	No	Yes	Exclude
Pattern C	Yes	No	Excule if there is no transaction all the way to 24 months ago.

Inflation impact =
var cy = SELECTEDVALUE('Calendar Table'[Year])
var cm = SELECTEDVALUE('Calendar Table'[Month Number])

var ly = cy - 1
var cy_items = filter('Data', and(YEAR([Received Date]) = cy, month([Received Date])=cm) )
var ly_items = filter(ALLSELECTED('Data'), and(YEAR([Received Date]) = ly, month([Received Date])=cm) )

var price_ly = SUMX(ly_items, [Order Unit Price]*[Received Quantity])/sumx(ly_items, [Received Quantity])
var price_cy = SUMX(cy_items, [Order Unit Price]*[Received Quantity])/sumx(cy_items, [Received Quantity])
var qty_cy = sumx(cy_items, [Received Quantity])

return (price_cy - price_ly) * qty_cy * -1

topic [Urgent help request] Monthly weighted average price this year vs last year in DAX Commands and Tips

[Urgent help request] Monthly weighted average price this year vs last year