Filter Changes in Rows

[Anonymous]

I have Salesforce data from reports that are run weekly. I want to get a series of rows that have shown any change since the previous week.

Sample Data:

Opportunity ID	Sales Rep	Stage	Revenue	Quantity	Report Date
12345	xyz	3	$1500	200	6/12/2020
45678	abc	2	$1200	115	6/12/2020
45678	abc	2	$1200	115	6/5/2020
12345	xyz	1	$1500	200	6/5/2020

I want to pull Opportunity 12345's row where Report Date is 6/12/2020 because its Stage changed from 1 to 3 over the two report dates. I know I'll need to write an expression with multiple checks, but I'm having trouble figuring out the first check. Assistance greatly appreciated!

[sturlaws · Accepted solution]

Hi @Anonymous 

if an Opportunity ID only have increasing or equal values for stage, meaning it will not go e.g. from stage 3 to stage 2, you can write a calculated column like this:

Has changed =
CALCULATE (
    MAX ( 'Table'[Stage] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Opportunity ID] = EARLIER ( 'Table'[Opportunity ID] )
            && 'Table'[Report Date] < EARLIER ( 'Table'[Report Date] )
            && 'Table'[Stage] <> EARLIER ( 'Table'[Stage] )
    )
)

If not you could write it like this:

Changed since last update =
VAR _oid =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Opportunity ID] ) )
VAR _currentDate =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Report Date] ) )
VAR _prevDate =
    CALCULATE (
        MAX ( 'Table'[Report Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Opportunity ID] = _oid
                && 'Table'[Report Date] < _currentDate
        )
    )
VAR _currentStage =
    CALCULATE ( SELECTEDVALUE ( 'Table'[Stage] ) )
VAR _prevStage =
    CALCULATE (
        VALUES ( 'Table'[Stage] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Opportunity ID] = _oid
                && 'Table'[Report Date] = _prevDate
        )
    )
RETURN
    IF ( _currentStage <> _prevStage && NOT ( ISBLANK ( _prevstage ) ), 1, 0 )

cheers,

Sturla 

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[Anonymous]

@sturlawsthank you very much for the reply! I'm trying to wrap my head around it. It looks like it's putting the updated stage at the old row, correct? For something like order quantity, I would then need to use the second version, since quantity can change to be lesser or greater, right? I've been doing DAX for a couple weeks now, and this is the most complex problem I've run into thus far.

[sturlaws]

It is a bit verbose, I agree, but writing it like this makes quite easy to understand what is happening in the code.

This being a calculated column, the dax code is evaluated row by row:

_oid will keep the opportunity id value of the row it is evaluating

_currentDate will keep the report date of the row it is evaluating

With calculated columns the row which is evaluated is the context, but it is possible to alter this context.

In _prevDate we want to find the previous report date relative to the row which is evaluated. So in the filter-part of the calculate we are using the ALL-function, which removes all filters on the table. And then apply our own filter, with opportunity id = _oid and report date < _currentDate(we are changing the context). Max of report date then returns the date of the previous entry for this opportunity id.

_currentStage is the stage of the row being evaluated

_prevStage is evaluated similar to the prevDate, using ALL to remove filters/change the context, and setting the context to _oid and _prevDate.

And then it is just a matter of checking whether current stage is different from previous stage. 

[Anonymous]

@sturlawsSorry to bother you with a bunch of replies, but if you have the time, I would greatly appreciate your feedback on a small issue that has arisen. I am extending the solution you wrote earlier to new fields, and am getting an unexpected result. I want to track revenue changes within the same project:

Opportunity ID	Amount	Report Date
12345	10000	6/12/2020
12345	5000	6/5/2020
12345	5000	5/28/2020

I have written the same formula that you suggested, except I changed the RETURN to be

RETURN
    IF ( _currentAmount <> _prevAmount, 1, 0 )

I would expect to get a new table with 1 in the first row and 0s in the last two. Instead, I get:

Opportunity ID	Amount	Report Date	amount_change
12345	10000	6/12/2020	1
12345	5000	6/5/2020	0
12345	5000	5/28/2020	1

5/28/2020 has no previous report date, so why does it throw a 1 for amount_change?

Again, thank you so much for all of your help thus far.

[sturlaws]

I think you forgot this part of the if-statement:

&& NOT ( ISBLANK ( _prevstage ) )

[Anonymous]

@sturlaws I had left that part out as our sales reps sometimes do not fully fill out fields in Salesforce, meaning that an Opportunity can have a blank stage when it is first created. If I include the NOT ISBLANK clause, will it be problematic for such Opportunities?

[sturlaws]

I would do a transformation in Power Query, changing stage to eg -1 if it is blank

[Anonymous]

@sturlawsmakes sense to me. thank you very much for your assistance! Have a great day

[Anonymous]

@sturlawsThank you so much for the detailed explanation; I really appreciate it. I'll use this solution and write it out to account for other columns that can change.