Forum Discussion
Filter Changes in Rows
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.
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.
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.
I think you forgot this part of the if-statement:
&& NOT ( ISBLANK ( _prevstage ) )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?
I would do a transformation in Power Query, changing stage to eg -1 if it is blank