Forum Discussion
Return datetime value based on value in another column
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!
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.
6 Replies
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/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?
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/
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"))
