Find min date if null based on multiple columns

Hi v-yanjiang-msft,

Sorry if it was unclear, I was getting a little frustrated yesterday and couldn't see the problem clearly. What I was looking for was the age of the order at a given status.

After having a sleep on it, I think I've worked out my biggest issue. I couldn't understand how I would determine whether the today's date (added on data capture) would be the place to determine the age of the status or whether to just use the received date for statuses before engineering completed.

What I have been able to work out is that I won't be able to accurately determine the age of an order at a given status unless the recieved date is greater than to equal to the 07/11/2022 (when I started to capture the data).

What I have done to resolve this is to create a new table from the data, group by the orderID, received date, eng comp date, completed date and status. I also aggregated DateToday as min so that it obtained the earliest date that it appeared in the data for that given status and called that FirstOnData.

I then created a new column

= Table.AddColumn(#"Changed Type", "Age", each if [Status] = "Complete (Not Invoiced)" then Duration.Days([TodaysDate] - [Completed Date]) else if [Status] = "Engineering Completed" then Duration.Days([TodaysDate]-[Eng Comp Sent Date]) else if [Received] < #date(2022,11,7) then Duration.Days([TodaysDate] - [Received]) else Duration.Days([TodaysDate] - [FirstOnData]))

This allows me to utilise the appropriate columns for eng comp and completed and also the DateToday if I have the data to use, if not it reverts to the recieved date to calculate the age

I hope this makes sense now and thank you for replying however, my issue is now resolved

Thanks