I'm trying to prove or disprove the notion that one of our products (let's call it "X") is cannabilizing revenue from the others ("Other").
My data model/structure is relatively straightforward, I have a fact table ("AllOrders") which is linked to a date table ("Calendar") and a customer table ("CustomerTable"). The orders are subscription based.
My first step was to define four unique measures:
1) Current Month $ X = CALCULATE( TOTALMTD( SUM( [Revenue[), 'Calendar'[DateKey]), AllOrders[Product] = "X")
2) Current Month $ Other = CALCULATE( TOTALMTD( SUM( [Revenue[), 'Calendar'[DateKey]), AllOrders[Product] = "Other")
Good news is that this works and captures the right value that I want in the month that X cannibalizes Other revenue, but I need to take it one step further.
Using an example:
My "Shifted Revenue" measure captures the 2,000 in 4/1/2017, but I also want it to continue on and pick up the subsequent months since that revenue is something I would consider cannibalized as well, not just the first month that it shifts from Other to X. Currently drawing blanks on how best to do this. Any ideas?