Advance with Assist shares quick solutions to common challenges encountered by the InterWorks Assist on-demand team.

Question: I have a need to determine my Current Opportunity amount and the change from whatever date was listed prior. It could be the previous week or several weeks ago. Any tips for figuring this out? The second requirement is that I need to aggregate to that week without showing the IDs.

This is an interesting question, but I can think of many needs and applications for this. As an opportunity moves through different updates within a sales stage, just knowing how long an opportunity has been sitting without an update, etc., would be valuable. This particular user was utilizing Salesforce data, but the application here could be anything with dates.

Since the user’s second requirement here was to aggregate by Current and Prior, that makes table calculations difficult, so we will be taking an LOD approach.

First, we need to find the Most Recent Date within each opportunity:

Now to find the Second-to-Last Date:

Next, create your boolean to keep the records you’re seeking:

After you’ve gone through these steps, you now have the ability to filter, group and create whatever else you may need with those dates.