Forum Discussion
Return Different Column Value in Same Row Context after CALCULATE
Hi Anonymous
please use
NextStageCreatedDate = VAR CurrentIndex = 'Opportunity History'[Indexnew] RETURN CALCULATE ( MIN ( 'Opportunity History'[StageName] ), ALLEXCEPT ( 'Opportunity History', 'Opportunity History'[OpportunityId] ), 'Opportunity History'[Indexnew] = currentindex - 1 )
Community ChampionHi Anonymous
my understanding as per the sample data you've presented that the CALCULATE filter shall return only one row. Therefore it is supposed to make no difference whether to use MAX or MIN or SLECTEDVALUE or even VALUES. If this is not the case that means the index column is not unique. If not then which column is? Is the date per opportunity unique for each eecord? Otherwise you can forst return the MIN or MAX date (based on your logic) then CALCULATE again filtering the date column by this value.
Hi Tamerj1,
Just wanted to thank you so much for the help - both solutions you provide work, and comparing the two together allowed me to actually understand how they work and what's going on behind the scenes. To answer this question - I wasn't aware that the CALCULATE filter only returned one row, I thought it the CALCULATE would return all rows related to an OpportunityID, and the correct date was then found by using MIN. Comparing the code that uses the CALCULATE with your other solution and how they both give the same output but do in in different ways helped me to understand the logic of CALCULATE.
As a side note, one thing I learned when comparing the formula is the second solution you provided (not using calculate) has rare chances when it could return a blank value when there actually should be a value, which the first solution (using calculate) doesn't miss. If the CreatedDate for two rows is the same down to the second, the solution without calculate won't recognize the CreatedDate as a new entry, and will return that there is no stage after when there actually is. While a rare occurance in the data, there are times when this would happen due to Salesforce automation rules doing bulk updates at once, causing multilpe rows to be created. Thanks again for all the help, I really appreicate the time and effort you put in to help me learn a bit more about how this all works 🙂
- tamerj1
Anonymous
Yes that is true. When you have duplicate dates then the date column won't work. But it is not the CALCULATE which makes the difference it is the unique index column. You can follow the same method but replacing the date column with the index column and the correct result is guaranteed. The method that does not contain CALCULATE is optimized in terms of performance and can be optimized further using TOPN function.
so happy that you learned out of this. Have a grat day!