Return datetime value based on value in another column

[macmy034]

Hey Guys;

I have a column which contains values, minutes past x time, I need to find the smallest value here then return a datetime from another column in the same table.

Eg

Person1   84   31/05/2022 07:24:00   

Person1   90   31/05/2022 07:30:00

Person1   95   31/05/2022 07:35:00

I need to return 31/05/2022 07:24:00 (Based on the 84 column)

I cant use earliest and latest time methods as it needs to be calulcated from a time I set, not midnight as powerbi does.

https://community.powerbi.com/t5/Desktop/Retrieving-a-value-based-on-another-column/m-p/311381 Helps alot, I just dont know how to pull a datetime instead of a summarized value e.g number.

Thanks!

[HansSu]

You can format the result including time. Make a column like:

Result = if (Table[number] = min(Table[number]) , format (Table {Date Time] , "DD/MM/YYYY & " " "HH:MM:SS"))

[VahidDM]

Hi @macmy034 

84 is the smallest  number in your table? Is there any other Person in your table?

You can use something like this:

Measure=
Var _A = calculate(min(table[value]),all(table))
return
calculate(max(table[date/time]),table[value]=_A)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[macmy034]

Sorry Yes, I have several people - and its over several different days too;

So im guessing I also need a 

Var Person = SELECTEDVALUE(table[person]))
Var Date = SELECTEDVALUE(table[date]))

then how do I add that into my calculate?

[VahidDM]

Can you post sample data as text and expected output?
Not enough information to go on;

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[macmy034]

Sorry guys; I figured it may be easier if I simplified it but I think that made it more complicated haha.

ShiftIndex is Date + 1 for D/S / 2 for N/S

Duration is time a person was in this status

SAP_ID is the Person

StartCalc is Shift Start time minus StartTime (time from shift start time in seconds)

EndCalc is the same, but minus Shift End time.

I need to find the status start time that is closest to the shift start time (smallest amount of start calc) then I need to find the end time closest to the shift end time (Smallest amount of endcalc)

Also: Since taking this screenshot i've found the cause of the duplicates and removed it too 🙂

[Icey · Accepted solution]

Hi @macmy034 ,

Is this what you want?

Calculated StartTime = [Shift Start Time]+#duration(0, 0, 0,[StartCalc])

Calculated EndTime = [Shift End Time]-#duration(0, 0, 0,[EndCalc])

Best Regards,

Icey

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