cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AliceW Member
Member

See the values as they were at a given date - Opportunity Field History

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 IDChanged FieldChange DateToFrom
111Stage1-Oct-1912
111Stage9-Oct-1923
111Stage20-Oct-1937
222Stage2-Oct-1913

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
lc_finance Member
Member

Re: See the values as they were at a given date - Opportunity Field History

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

 

Image 2019-10-21 at 7.17.12 PM.png

Do not hesitate to let me know if you have any more question,

 

LC

View solution in original post

11 REPLIES 11
epappu Frequent Visitor
Frequent Visitor

Re: See the values as they were at a given date - Opportunity Field History

Hi Alice,

 

Will the filter be comprised of only date?

 

If the answer to the above is YES, then you can do the following:

1. Add a Slicer visual to the page.

2. Select the date column from your table to add to this slicer.

3. Once that is added, you can go to that visual and click on the dropdown arrow (which is on the top right of the visual)

   And select list in the available options.

4. Now the user can select any date they chose and the table will reflect all the data related to that date.

 

If my understanding of your question is incorrect, please do let me know.

AliceW Member
Member

Re: See the values as they were at a given date - Opportunity Field History

Thanks for replying so quickly! Unfortunately, it's more complicated than that.

The date column simply states when the change took place. Take the first two lines, for example: they say that Opportunity #111 was in stage 2 from October 1st to October 9th.

If the user selects, say, October 2nd, so a date in between, this Opportunity should appear.

Super User
Super User

Re: See the values as they were at a given date - Opportunity Field History

Hi @AliceW 

I have doubts as to some details of what you need, but try this and perhaps we can refine it later if necessary:

1. Place Table1[Opportunity ID] in the rows of a table visual

2. We assume a calendar table with no relationship with Table1. Place CalendarTable[Date] in a slicer that you use to select the date

3. Create this measure :

Show Measure =
VAR FirstDate_ =
    CALCULATE ( MIN ( Table1[Change Date] ) )
VAR LastDate_ =
    CALCULATE ( MAX ( Table1[Change Date] ) )
RETURN
    IF (
        SELECTEDVALUE( 'CalendarTable'[Date] ) >= FirstDate_ && 'CalendarTable'[Date] <= LastDate_,
        1,
        0
    )

4. Place the measure in the visual filter for the table and select to show when value is 1

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

AliceW Member
Member

Re: See the values as they were at a given date - Opportunity Field History

Hi @AlB, I have the feeling we're getting close!

I've done that with a little correction (I think) - I've added SELECTEDVALUE for the last calendar date, as it would show an error.

Anywho, afterwards the Calendar seems to show only the dates where changes have occured. Weird, right?

If you think it helps, this is my .pbix file Sample Changes 

Thank you so much!

lc_finance Member
Member

Re: See the values as they were at a given date - Opportunity Field History

Hi @AliceW ,

 

 

here is the solution I propose to you:

1) Create a new calendar table with the DAX formula CALENDARAUTO()

2) Add a calculated column to the Sales Opportunities Table to show the next change date. Here is the formula:

Next Change Date = 

VAR currentTo = [To]

VAR tableOfNextChangeDates = CALCULATETABLE(
    VALUES('Salesforce Opportunities'[Change Date]),
    ALLEXCEPT('Salesforce Opportunities','Salesforce Opportunities'[Opportunity ID],'Salesforce Opportunities'[Changed Field]),
    'Salesforce Opportunities'[From]=currentTo
)

RETURN MINX(tableOfNextChangeDates,[Change Date])

3) Create a new parameter 'Stage' for the user to choose the Stage he is interested in

4) Create a new calculated measure 'Opportunities available' to find out the available opportunities. DAX formula:

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 && [Changed Field]=selectedField && [To]=selectedStage && [Change Date]<=selectedDate && [Next Change Date]>selectedDate, 1, BLANK())
)

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

That's it!

Here is what it looks like:

Image 2019-10-21 at 6.13.05 PM.png

 

And you can download from here the Power BI template.

 

Let me know if this helps you!

 

LC

Interested in learning Power BI and DAX? Check out my blog at www.finance-bi.com

Super User
Super User

Re: See the values as they were at a given date - Opportunity Field History

Hi @AliceW 

You're right, that SELECTEDVALUE was missing. What I suggested earlier works, see it in the attached file. You have to set the measure as visual filter. And in the visual table you should have only the ID as described earlier. If you want all the columns of the table we'd need to change the code of the measure (plus you would have to define exactly what rows with that ID you want to show, is it all of them?)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

AliceW Member
Member

Re: See the values as they were at a given date - Opportunity Field History

@AlB, sorry, I'd like the table to show all of the columns related to the Opportunities. In addition, I'll need to build some measures, such as the number of Opportunities in the selected date (by the way, only one should be possible). Thanks again!!

 

AliceW Member
Member

Re: See the values as they were at a given date - Opportunity Field History

@lc_financethank you! It looks amazing!

Just a couple of questions, please.

If I were to only keep the requirement of selecting a date, would this be the formula? (I don't want a Stage or a Field Name to be mandatory - some people might just want to see just what Opportunities were present at a date, and in what stage).

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 && [Next Change Date]>selectedDate, 1, BLANK())
)

RETURN IF(opportunityIsPresent>0, "Opportunity", BLANK())
 
Also, the report shows both Opportunities when I select a date in, for example, January. Nothing should be displayed...
Thank yoU!!
 
lc_finance Member
Member

Re: See the values as they were at a given date - Opportunity Field History

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

 

Image 2019-10-21 at 7.17.12 PM.png

Do not hesitate to let me know if you have any more question,

 

LC

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 278 members 2,603 guests
Please welcome our newest community members: