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=10...
| | 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 |
