Forum Discussion
Comparing Across Different Dates in a Single Table
Hello!
I've got data from a daily snapshot that lists out unique opportunities, all of which have a key (Opportunity ID, column D). What I want to do is compare across dates to see how the opportunity has changed (different forecast category, different bookings revenue amount, different close date). The way I've done this so far is to just focus on two snapshot dates and merge those queries on the Opportunity ID column. However, I want to change it so that a user could pick two dates (based on the Snapshot Date column) to compare across--that way a user could control how the time period they want to look at.
In this sample data, I've scaled it down so I only have one opportunity for each snapshot date, but in my real data I have thousands of opportunities for each date. Another complication is the fact that I don't just want to know where my initial snapshot opportunities are, but also if any new opportunities showed up in the time period I'm looking at. (I.e. if on the 6/12/2022 snapshot I have opportunities A, B, C, and D set to close in Q4 and then I compare it to a 7/12/2022 snapshot, I want to not only know where A, B, C, and D are but I also want to know if there are any new opportunities (E, F, etc) that are projected to close in Q4.
Any help is much appreciated!
Ok, I think I figured it out:
Create a measure that captures the snapshot date you want to compare to (in blue):
01_CompSSDate = SELECTEDVALUE('Comparison Snapshot'[Snapshot Date])Create measures that capture your initial snapshot data at the row level (this is just one example for opportunity ID, but they follow the same pattern):01_InitialOptyID = CALCULATE(MAX('Initial Snapshot'[Opportunity ID]))Then create measures that lookup the corresponding opportunity ID and snapshot date from the comparison data (in red):01_CompForecastCategory = LOOKUPVALUE('Comparison Snapshot'[Forecast Category], 'Comparison Snapshot'[Snapshot Date], [01_CompSSDate],'Comparison Snapshot'[Opportunity ID], [01_InitialOptyID])The last columns (in green) are just simple calculations or IF statements!
3 Replies
aklingensmith , You need one or two independent date.
I covered an example here
How to use two Date/Period slicers
In your video it seems like your solution resulted in a longer table that was able to include dates from two different date slicers, which is different from what I'm trying to do (I think).
Just using simple date slicers to select the initial snapshot and the comparison snapshot, I can essentially see the data I want:What I need now is a way to get these in a wider table. I'm trying to do the following:
- Get all of the data on one line so as you look left to right you can see where the opportunity was and where it landed based on the two snapshot dates
- Create new columns (from measures, I assume) where I can apply labels based on the movement (for example, in this picture I'd want to label this opportunity a "Pull" because the reporting close date went from Q4 to Q3)
- Create a new column (again from measures, I assume) showing the opportunity amount growth (in this case, $100,000 between the two snapshot dates)
- Ensure that opportunities that were in my 7/4/2022 snapshot that weren't in my 5/23/2022 snapshot are visible as well as those are new opportunities.
I've been able to do all of these in Power Query, but because that's behind the scenes, a user wouldn't have the ability to select a different snapshot date.
Appreciate you taking the time to share a reply and try and help, though!Ok, I think I figured it out:
Create a measure that captures the snapshot date you want to compare to (in blue):
01_CompSSDate = SELECTEDVALUE('Comparison Snapshot'[Snapshot Date])Create measures that capture your initial snapshot data at the row level (this is just one example for opportunity ID, but they follow the same pattern):01_InitialOptyID = CALCULATE(MAX('Initial Snapshot'[Opportunity ID]))Then create measures that lookup the corresponding opportunity ID and snapshot date from the comparison data (in red):01_CompForecastCategory = LOOKUPVALUE('Comparison Snapshot'[Forecast Category], 'Comparison Snapshot'[Snapshot Date], [01_CompSSDate],'Comparison Snapshot'[Opportunity ID], [01_InitialOptyID])The last columns (in green) are just simple calculations or IF statements!
