Find oldest date in column, then add a value from another column (without using calculated column)

[frano72]

Hi, I've got what I thought was a relatively straight forward DAX issue but cannot find a way of doing it in a measure versus a calculated column.

I have a column of UTC dates and in an adjoining column the appropriate UTCOffset value in minutes to convert to NSW timezone.

I want to find the oldest value in the UTC Date column (so filter down to a single row) and then add the value in the UTCOffset column for that row.

I can do it by using a calculated column but thought it might be a waste of memory to do that calculation for all the dates when I only need to do it on a single date AFTER the minimum has been found.

Any ideas ?

StartTimeUTC	UTCOffsetMin
28/05/2020 9:00	600
28/05/2020 10:00	600
28/05/2020 11:00	600

[amitchandak · Accepted solution]

@frano72 , try

https://docs.microsoft.com/en-us/dax/firstnonblankvalue-function-dax

firstnonblankvalue(StartTimeUTC,Min(UTCOffsetMin))

[frano72]

Hey @amitchandak , almost what i wanted and you got me on the right track - thanks !

firstnonblankvalue(StartTimeUTC,Min(StartTimeUTC))    - this one returns the oldest UTC value

firstnonblankvalue(UTCOffsetMin,Min(StartTimeUTC))    - this one returns the UTCoffset for the oldest UTC value

then just needed to add them together to convert to local time.

Thanks !!!!!!