Return Different Column Value in Same Row Context after CALCULATE

[Anonymous]

Hi all,

I hope everyone is doing well. I am new to PowerBI and am currently working with Salesforce data to analyze historical opportunity changes. The data I am currently working on looks something like this:

OpportunityID	StageName	CreatedDate	Index	NextStageCreatedDate
1234	Stage 1	9/1/2021 8:43:24 AM	1	9/3/2021 10:32:89 AM
1234	Stage 2	9/3/2021 10:32:89 AM	2
1236	Stage 5	12/1/2021 2:45:12 PM	3
1237	Stage 7	8/31/2020 12:12:56 PM	4	9/5/2020 11:28:41: AM
1237	Stage 6	9/5/2020 11:28:41 AM	5	9/13/2020 9:41:48 AM
1237	Stage 8	9/13/2020 9:41:48 AM	6

This data is a hisotry of changes made to the opportunity. At first, I only had the first three colums (Opportunity ID, StageName, CreatedDate). Looking for a way to measure the time between different entries, I stumbled upon this post from 2017 of someone who was trying to do the same thing as me. Following the solution to this post worked perfectly, and is what gave me both the Index and NextStageCreatedDate. Here is the solution that was provided:

1. Open query editor. Sorted by "OpportunityId";
2. Open ADVANCED EDITOR (Sign 2), then add ", {"CreatedDate", Order.Ascending}" (sign 3).
3. Click DONE, you will see two sorted arrows (in yellow square).This is what we want.
4. Add index.
5. Add a calculated column with this formula.

NextStageCreatedDate =
VAR CurrentIndex = 'Opportunity History'[Indexnew]
RETURN
    CALCULATE (
        MIN ( 'Opportunity History'[CreatedDate] ),
        ALLEXCEPT ( 'Opportunity History', 'Opportunity History'[OpportunityId] ),
        'Opportunity History'[Indexnew]
            = currentindex + 1
    )

This code will return the next CreatedDate for an entry with the same OpportnityID; if there are no next entries for that OpportunityID, the field will be left blank. 

Now that I have the NextStageCreatedDate column created and working, I am now trying to create a column that will give me the StageName the OpportunityID was previously at before being changed. Seeing as the row context will have the same exact criteria as above, I thought it would be best to keep the same funciton and alter it slightly to instead return the StageName. I first altered the code to pull from the previous entry as opposed to the next. To accomplish this, I changed

= currentindex + 1

to 

=currentindex - 1

Current issue: I am not sure how to alter the formula to return the StageName rather than the CreatedDate. I have explored using RELATED, RELATEDTABLE, and LOOKUPVALUE, among others (but I'm not sure where I should input these, if they are correct). Since the CALCULATE along with MIN and ALLEXCEPT is used to determine the correct row context as well as ignores filters, I believe I need to (or should) keep it when determining the previous stage. When determining the previous StageName, I want it to calculate the row context in the exact same way it did for the NextStageCreatedDate (outside of me changing the last line to allow for previous instead of next), but instead of returning the CreatedDate it found, I would like the StageName from the same row (in the same table) as the CreatedDate was. Additionally, rather than returning a blank if there is no previous entry, I would like it return "First".

Could anyone help me figure out how I could accomplish the above? Since I'm new to PowerBI, I would greatly appreciate any explanations or advice you may have that could help me along my journey. Thanks in advance all of those that take time to help me out with this.

[tamerj1]

HI @Anonymous 
I've recreated the DAX following different approach that requires no index column. I've been using this method for a quite long time and it never fails. Refer to sample file with the solution for both calculated columns https://we.tl/t-Yxd4gbggQB

NextStageCreatedDate = 
VAR CurrentDate = 'Opportunity History'[CreatedDate]
VAR CurrentStageTable =
    CALCULATETABLE ( 'Opportunity History', ALLEXCEPT ( 'Opportunity History','Opportunity History'[OpportunityID] ) )
VAR NextDatesTable = 
    FILTER ( CurrentStageTable, 'Opportunity History'[CreatedDate] > CurrentDate )
VAR NextDate = 
    MINX ( NextDatesTable, 'Opportunity History'[CreatedDate] )
RETURN
    NextDate

PreviousStageName = 
VAR CurrentDate = 'Opportunity History'[CreatedDate]
VAR CurrentStageTable =
    CALCULATETABLE ( 'Opportunity History', ALLEXCEPT ( 'Opportunity History','Opportunity History'[OpportunityID] ) )
VAR PreviousDatesTable = 
    FILTER ( CurrentStageTable, 'Opportunity History'[CreatedDate] < CurrentDate )
VAR PreviousDate = 
    MAXX ( PreviousDatesTable, 'Opportunity History'[CreatedDate] )
VAR PreviousStage =
    MAXX ( FILTER ( PreviousDatesTable, 'Opportunity History'[CreatedDate] = PreviousDate ), 'Opportunity History'[StageName] )
RETURN
    PreviousStage

[tamerj1 · Accepted solution]

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
    )

[Anonymous]

Hi Tamerj1,

Thanks so much for helping me with this. When trying to figure this out on my own, I actually tried the above and saw that it worked, but thought that it was incorrect to use 

MIN ( 'Opportunity History'[StageName] )

My reasoning: On the MIN fuction page on dax guide, it states that strings are compared according to alphabetical order when using the MIN function. I was under the impression that by changing

MIN ( 'Opportunity History'[CreatedDate] ),

to 

MIN ( 'Opportunity History'[StageName] ),

 I would no longer be locating the entry under the same OpportunityID that was previously created, but would instead be locating the entry under the same OpportunityID which has the lowest alphabetical ranking for StageName. Is there any chance you could help my understand why this isn't the case?

I recognize that the function doesn't do that, I'm just confused why using StageName with MIN wouldn't return the same StageName for all opportunities with the same OpportunityID, for example:

OpportunityID	StageName	CreatedDate	Index	NextStageCreatedDate
1234	Stage A	9/1/2021 8:43:24 AM	1	9/3/2021 10:32:89 AM
1234	Stage B	9/3/2021 10:32:89 AM	2
1234	Stage C	12/1/2021 2:45:12 PM	3

If you were to find the Min of [StageName] why wouldn't it always return row 1, since Stage A would be alphabetically before all other entries? Thanks again for the help - I really appreciate it

[tamerj1]

Hi @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. 

[Anonymous]

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!