Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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")
3) Prior Month $ X = CALCULATE( SUM( [Revenue]), PREVIOUSMONTH('Calendar'[DateKey]), AllOrders[Product] = "X")
4) Prior Month $ Other = CALCULATE( SUM( [Revenue]), PREVIOUSMONTH('Calendar'[DateKey]), AllOrders[Product] = "Other")
Using these four measures, I created a fifth measure called "Shifted Revenue".
5) Shifted Revenue =
=CALCULATE ( MIN ( [CM $ X] - [PM $ X], [PM $ Other] - [CM $ Other] ), FILTER ( CustomerTable, [CM $ X] > [PM $ X] && [CM $ Other] < [PM $ Other] && TOTALMTD ( [Sum of Revenue], 'Calendar'[DateKey] ) > 0 ) )
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?
Thanks for your time!
For the first step, instead of measure, you may try adding a calculated table.
Hi @v-chuncz-msft,
Appreciate the reply - not sure what that would accomplish? I'm somewhat familiar with the concept of calculated tables, but haven't really worked with them.
Hi @ccsrtw
Can you post some sample data on one drive and share the link here and the output expected.
Cheers
CheenuSing
@CheenuSing - sure! I'll mock something up and post later today or tomorrow. Appreciate your reply.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |