Forum Discussion
Filter Changes in Rows
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!
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.
9 Replies
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.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.
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.