Forum Discussion
Post PartisanCalculate Max and Min from another table
How can I get value for MAX timestamp and MIN timestamp in Table 1 from Table 2 as per below scenerio-
learner03 apologies, I didn't catch that requirement in your initial description.
How about these:MIN Timestamp = VAR _USERNAME = Table1[UserName] VAR _DATE = FORMAT(Table1[Date], "dd/mm/yyyy") VAR _Result = CALCULATE( MIN(Table2[Timestamp]), Table2[User] = _USERNAME, FORMAT(Table2[Timestamp], "dd/mm/yyyy") = _DATE ) Return _ResultMAX Timestamp = VAR _USERNAME = Table1[UserName] VAR _DATE = FORMAT(Table1[Date], "dd/mm/yyyy") VAR _Result = CALCULATE( MAX(Table2[Timestamp]), Table2[User] = _USERNAME, FORMAT(Table2[Timestamp], "dd/mm/yyyy") = _DATE ) Return _Result
4 Replies
learner03 assuming you are trying to do this as a calculated column in Table 1, you could use something like:
MIN Timestamp = VAR _USERNAME = Table1[UserName] RETURN CALCULATE( MIN(Table2[Timestamp]), Table2[User] = _USERNAME )and
MAX Timestamp = VAR _USERNAME = Table1[UserName] RETURN CALCULATE( MAX(Table2[Timestamp]), Table2[User] = _USERNAME )
learner03I tried with this, but getting minimum time from whole Table 2 and Max time from whole Table 2 like below-
where as I want min time of 21/07/201 and Max time of 21/07/201, and similarly min time of 22/07 amd max time of 22/07 and so on . So, basically min and max time of the date.
learner03 apologies, I didn't catch that requirement in your initial description.
How about these:MIN Timestamp = VAR _USERNAME = Table1[UserName] VAR _DATE = FORMAT(Table1[Date], "dd/mm/yyyy") VAR _Result = CALCULATE( MIN(Table2[Timestamp]), Table2[User] = _USERNAME, FORMAT(Table2[Timestamp], "dd/mm/yyyy") = _DATE ) Return _ResultMAX Timestamp = VAR _USERNAME = Table1[UserName] VAR _DATE = FORMAT(Table1[Date], "dd/mm/yyyy") VAR _Result = CALCULATE( MAX(Table2[Timestamp]), Table2[User] = _USERNAME, FORMAT(Table2[Timestamp], "dd/mm/yyyy") = _DATE ) Return _Result
- learner03
Thanks . Just a follow up Question--
How can I get difference of the Start and end time in hours (like whole number example 5,6.5 etc)-