Need help with DAX formula for updating the column based on another column changes

[Roy_W]

I have a table called "Original" as follows. I need to add a new column, temporarily named "nw_updated_date" (referencing "Updated"). The logic is to update the "nw_updated_date" only when the "reason" changes. For example, in the case of id = 1, when rev changes from 3 to 7 and the "reason" is "assigned" for all these rows, then "nw_updated_date" should be "07:02:06AM".

Original:
id,rev,reason,updated_date
1,1,awaiting,11/13/2023 10:02:06AM
1,2,awaiting,11/13/2023 11:02:06AM
1,3,assigned,11/14/2023 07:02:06AM
1,4,assigned,11/14/2023 09:02:06PM
1,5,assigned,11/15/2023 09:02:06PM
1,6,assigned,11/16/2023 09:02:06PM
1,7,assigned,11/17/2023 09:02:06PM
1,8,awaiting,11/18/2023 07:02:06PM
2,1,awaiting,11/13/2023 07:02:06PM
2,2,awaiting,11/15/2023 10:02:06PM

Updated:
id,rev,reason,updated_date,nw_updated_date
1,1,awaiting,11/13/2023 10:02:06AM,11/13/2023 10:02:06AM
1,2,awaiting,11/13/2023 11:02:06AM,11/13/2023 10:02:06AM
1,3,assigned,11/14/2023 07:02:06AM,11/14/2023 07:02:06AM
1,4,assigned,11/14/2023 09:02:06PM,11/14/2023 07:02:06AM
1,5,assigned,11/15/2023 09:02:06PM,11/14/2023 07:02:06AM
1,6,assigned,11/16/2023 09:02:06PM,11/14/2023 07:02:06AM
1,7,assigned,11/17/2023 09:02:06PM,11/14/2023 07:02:06AM
1,8,awaiting,11/18/2023 07:02:06PM,11/18/2023 07:02:06PM
2,1,awaiting,11/13/2023 07:02:06PM,11/13/2023 07:02:06PM
2,2,awaiting,11/15/2023 10:02:06PM,11/13/2023 07:02:06PM

Can you please help me with the DAX formula for this?

I also attached sample PBI file, please check.

PBI_file 

[Anonymous · Accepted solution]

Hi @Roy_W ,

Below is my table:

The following DAX might work for you:

new update date = 
     IF(
        'Table'[Id] == 1 && 'Table'[reason] = "assigned" ,
        CALCULATE(
        MIN('Table'[updated_date]),
        FILTER('Table','Table'[Id] == 1 && 'Table'[reason] = "assigned")),
        'Table'[updated_date]
    )

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Rupak_bi]

try this

new update date =

calculate(min(Query1[updated_date]),allexcept(Query1,Query1[id],Query1[reason]))