Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AliceW
Impactful Individual
Impactful Individual

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

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
lc_finance
Solution Sage
Solution Sage

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

AliceW
Impactful Individual
Impactful Individual

@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!!
 

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

AliceW
Impactful Individual
Impactful Individual

LC, you rock! I'm also making your blog one of my favorites 🙂

About me seeing Opps that weren't there in your screenshot - I figured it out! I've added another field: "From". But I've added, as a filter to the visual, "Opportunities available" as "is not blank".

Thank you so much.

Alice

Perfect.

 

Thank you for following my blog! I love BI and helping out people so I hope I can do more and more of this in the future.

 

Do not hesitate if you need more help,

 

LC

 

AlB
Super User
Super User

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
Impactful Individual
Impactful Individual

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!

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
Impactful Individual
Impactful Individual

@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!!

 

Anonymous
Not applicable

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
Impactful Individual
Impactful Individual

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.