Calculate the number of days an item was in a particular status based on different columns

[Aurick]

I've only recently started learning Power BI and I'm trying to figure out how to show the age of an a particular ID as the status changes.  Below is an example of how the data is set up.   I'm trying to come up with a DAX that will look match up the new value to the prior value, and then give me the difference in days.  So for example, ID 1 was created on 2/1 with a brand new value of "new" and then someone looked at it and updated it on 2/2, so that would be 1 day.  

I'm getting caught up in how I get it to find that same ID number based on the new value to the Prior value. Any guidance would be appreciated! Thank you! 

ID	Edited	Prior Value	New_vaule
1	2/1/2024		New
1	2/2/2024	New	In Review
1	2/4/2024	In Review	Approval
1	2/6/2024	Approval	Completed

[Aurick]

Thank you all for your help. 

[v-xuxinyi-msft · Accepted solution]

Hi @Aurick 

You can create a calculated column as follows.

datediff = 
VAR _prior = CALCULATE(MAX([Edited]), FILTER('Table', [Edited] <= EARLIER('Table'[Edited]) && [New_vaule] = EARLIER('Table'[Prior Value]) && [ID] = EARLIER('Table'[ID])))
RETURN
DATEDIFF(_prior, [Edited], DAY)

Is this the result you expect?

Best Regards,
Yulia Xu

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

[Aurick]

I think it's getting me there, I should have mentioned that I"m using May 2022 version (company choice) and when I try to enter what you provided in "Offset" isn't showing as a function

[Daniel29195]

@Aurick 

output

calculated column

Column 2 = 
var ids = 'Table'[ID]
var current_date =  'Table'[Edited]
var ds = 
FILTER(
    'Table',
    'Table'[ID] = ids && 'Table'[Edited] <current_date
)

var prev_date  = 
SELECTCOLUMNS(
    TOPN(
        1,
        ds,
        'Table'[Edited],
        DESC
    ),
    'Table'[Edited]
)

var res =  DATEDIFF(prev_date, current_date , DAY)

return res

let me know if this works for you .

if it does, please mark as accepted solution, so you can help others to find it more easily. 

[Daniel29195]

@Aurick 

sample data : 

calculated column : 

Column = 
var current_date =  'Table'[Edited]
var prev_date = 
SELECTCOLUMNS(
OFFSET(
    -1,
    SUMMARIZE(
        'Table',
        'Table'[ID],
        'Table'[Edited]
    ),
    ORDERBY('Table'[Edited], ASC),
    PARTITIONBY('Table'[ID])
),'Table'[Edited])
return DATEDIFF(prev_date , current_date ,DAY)

let me know if this works for you .

if it does, please mark as accepted solution, so you can help others to find it more easily.