Get MIN and MAX time per user per day in table?

[jd8766]

Hi, I have a table that looks like the below..

I want to show by each day, what the total number of calls was by user, and also the FIRST and LAST time a call was made per user.

My ideal output would be the below..

I have tried the MAX and MIN functions but they return the MAX time in my entire time dimension.. which isn't what I wanted. Any ideas how I can achieve the below?

[v-jianboli-msft · Accepted solution]

Hi @jd8766

You can achieve this directly by using MIN and MAX:

MINTime = MIN('Table'[Time])
MaxTime = MAX('Table'[Time])

Then change the Format of the measures:

Output:

If your data is more complex, please try:

First Time = CALCULATE(MIN('Table'[Time]),FILTER(ALL('Table'),[Date]=MAX('Table'[Date])&&[User]=MAX('Table'[User])))
Last Time = CALCULATE(MAX('Table'[Time]),FILTER(ALL('Table'),[User]=MAX('Table'[User])&&[Date]=MAX('Table'[Date])))

Output:

If you need calculated column:

FIRSTTIME = CALCULATE(MIN('Table'[Time]),FILTER('Table',[User]=EARLIER('Table'[User])&&[Date]=EARLIER('Table'[Date])))
LASTTIME = CALCULATE(MAX('Table'[Time]),FILTER('Table',[User]=EARLIER('Table'[User])&&[Date]=EARLIER('Table'[Date])))

Output:

If you need a table:

Table 2 =
SUMMARIZE (
    'Table',
    'Table'[User],
    'Table'[Date],
    "Total Call",
            SUM ( 'Table'[Calls] ),
    "First Time",
            MIN ( 'Table'[Time] ),
    "Last Time",
            MAX ( 'Table'[Time] )
)

Output:

 

Best Regards,

Jianbo Li

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

[amitchandak]

@jd8766 , If you create min/max measure thtat will work

max(Table[time])

min(Table[time]) 

if you need a column

minx(filter(Table, [User] = earlier([User]) ),[Time])