Solved: See the values as they were at a given date - Oppo... - Page 2

AliceW · ‎10-21-2019

Hi everyone,

I was wondering if anyone can help me with this issue.

What I have:

A table with Salesforce Opportunities and their main changed fields. Each row includes (among other cool things) this data: the Opportunity ID, the name of the field, when the change took place, what was the value being replaced, and what it was replaced with.

Opportunity ID	Changed Field	Change Date	To	From
111	Stage	1-Oct-19	1	2
111	Stage	9-Oct-19	2	3
111	Stage	20-Oct-19	3	7
222	Stage	2-Oct-19	1	3

What I'm looking for:

To enable the user to see what Opportunities were in, say, Stage 2, at a given date he selects on the spot (in a filter).

For example, if October 3rd is selected, the selection would comprise of only Opportunity #111.

If the user selects October 2nd, the will see two Opportunities (#111 and #222).

Some thoughts:

Perhaps a first step would be to build a new column with the previous change date? I don't know how to do that though.

Then create a separate date table for the filter the user to employ? I'm lost.

Help, please!

Thank you,

Alice

lc_finance · ‎10-21-2019

Hi @AliceW ,

I am very glad this is what you were looking for!

Here is the updated formula by removing Stage and Field name. I also improved it to take into account the rows with an empty next change date

Opportunities available = 

VAR selectedDate = LASTDATE('Date'[Date])
VAR selectedField = SELECTEDVALUE('Salesforce Opportunities'[Changed Field])
VAR selectedOpportunity = SELECTEDVALUE('Salesforce Opportunities'[Opportunity ID])
VAR selectedStage = [Stage Value]

VAR opportunityIsPresent =  COUNTX('Salesforce Opportunities',
IF([Opportunity ID]=selectedOpportunity && [Change Date]<=selectedDate && OR([Next Change Date]>selectedDate,[Next Change Date]=BLANK() ), 1, BLANK())
)

RETURN IF(opportunityIsPresent>0, "Opportunity", BLANK())

What do you mean that when you select January it shows both opportunities? I select January and I see 0 opportunities.

Here is my screenshot