I created a table (table name: Snapshot 1) using salesforce opportunity snapshots where the opportunity table is captured every 1st of each month and tagged every records with Snapshot Date (e.g. 4/1/2019, 5/1/2019, etc.). I then duplicated Snapshot 1 as Snapshot 2 and linked using opportunity id, many-to-many cardinality, cross-filter direction is Snapshot 1 filters Snapshot 2.
I have 2 snapshot date slicers - one for Snapshot 1 and another one for Snapshot 2. In my testing, I set Snapshot 1 filter to 7/1/2019 and Snapshot 2 filter to 4/1/2019. I also filtered Snapshot 1 stage = Closed-Won. I then created a matrix with Opportunity Stage from Snapshot 2 in Rows and Opportunity Stage from Snapshot 1 in Columns. I then added the amount field from both snapshot tables to Values.
Here’s a screenshot of the resulting table. I’m getting the correct amounts from Snapshot 2 (since S1 filters S2). But in the Snapshot 1 amount column, I couldn’t split the amount (total won of 23.9M) among the stages from Snapshot 2. Basically, I want to know the composition of the 23.9M won deals in July based on what the stage was back in April.
Here’s my current measure: CALCULATE(SUM(‘Opportunity Snapshots 1’[Line Amount]))
How do I modify this so that the Snapshot 1 amount gets filtered by the Snapshot 2 stages?