DAX to return the date a row Entered a given Status

[ChrisW764]

Hi peeps,

Really hoping someone can help me with this as it's been doing my  head in for ages!!

I have a table called "Status Change" with columns as below.  I have provided sample values filtered for a single Acc_ProjectChangeRequest__c.

Acc_ProjectChangeRequest__c	CreatedDate	Acc_PreviousProjectChangeRequestStatus__c	Acc_NewProjectChangeRequestStatus__c	RankByDate
a0GP3000002gp9vMAA	01/12/2023 18:05	Submitted to Approver2	In Review with CompanyName	8
a0GP3000002gp9vMAA	01/12/2023 18:05	In Review with CompanyName	Submitted to Approver2	7
a0GP3000002gp9vMAA	01/12/2023 15:04	Submitted to CompanyName	Submitted to Approver2	6
a0GP3000002gp9vMAA	01/12/2023 14:31	Queried by CompanyName	Submitted to CompanyName	5
a0GP3000002gp9vMAA	01/12/2023 13:05	Submitted to CompanyName	Queried by CompanyName	4
a0GP3000002gp9vMAA	30/11/2023 07:44	Submitted for Approver1	Submitted to CompanyName	3
a0GP3000002gp9vMAA	29/11/2023 15:01	Draft	Submitted to Approver1	2
a0GP3000002gp9vMAA	29/11/2023 14:12		Draft	1

The goal is to add a new column called "EnteredStatus" which returns the CreatedDate for the corresponding row where Acc_NewProjectChangeRequestStatus__c = the current record value for Acc_PreviousProjectChangeRequestStatus__c, i.e. the date the row entered the Status it's now changing from. (hope that makes sense!!)

In many cases a unique Acc_ProjectChangeRequest__c may bounce back and forward between the same Status pairs a couple of times, so I have added a custom column called RankByDate that adds an incremental number to rows that share the same Acc_ProjectChangeRequest__c - the idea being to then (somehow) use this to return the CreatedDate for the most recent time a record entered a given status.

But now I'm well and truly stuck!!  I have tried all sorts of code and even reached out to ChatGPT for help, but simply cannot figure out how to add this new "EnteredStatus" column so it returns the value I need for each row.

Please help!! 😩

[v-heq-msft · Accepted solution]

Hi @ChrisW764 ,

Here some steps that I want to share, you can check them if they suitable for your requirement.

Here is my test data:

1.Create a calculate column

EnteredStatus = 
CALCULATE (
        MAX ( 'Status Change'[CreatedDate] ),
        FILTER (
            'Status Change',
            'Status Change'[Acc_ProjectChangeRequest__c] = EARLIER ( 'Status Change'[Acc_ProjectChangeRequest__c] )
                && 'Status Change'[Acc_NewProjectChangeRequestStatus__c] = EARLIER ( 'Status Change'[Acc_PreviousProjectChangeRequestStatus__c] )
                && 'Status Change'[RankByDate] < EARLIER('Status Change'[RankByDate]
        )
    )
)

2.Final output

Best Regards,

Albert He

[ChrisW764]

Hi @v-heq-msft, 

You are a star!!  😁  

This code works perfectly - thank you so much! 

[ChrisW764]

To summairse, I need the DAX to return the CreatedDate of the record that matches the following criteria;

1. Acc_NewProjectChangeRequestStatus__c = Acc_PreviousProjectChangeRequestStatus__c (current record)
2. RankByDate value < RankByDate value (current record)
3. Acc_ProjectChangeRequest__c is the same as current record