cancel
Showing results for
Did you mean:
Highlighted
PrachiD Regular Visitor

Calculate Time Statisctics Hello All,
I m trying to calculate the In time and out time of Emp.
The problem is that a single person can do multiple times IN/OUT but I want to calculate only his First IN and  Last Out.
The Green column is the derived one which i have calculated for out statistics using below dax:

_Time_Statistics_out = SWITCH(TRUE(),
(Time data[time])<TIMEVALUE("17:00:00"),"Before 5:00",
(Time data[time])>=TIMEVALUE("17:00:00")&& (Time data[time])<=TIMEVALUE("17:30:00"),"5:00-5:30",
(Time data[time])>TIMEVALUE("17:30:00") && (Time data[time])<=TIMEVALUE("18:00:00"),"5:30-6:00",
(Time data[time])>TIMEVALUE("18:00:00") && (Time data[time])<=TIMEVALUE("18:30:00"),"6:00-6:30",
(Time data[time])>TIMEVALUE("18:30:00") && (Time data[time])<=TIMEVALUE("19:30:00"),"6:30-7:00",
(Time data[time])>TIMEVALUE("19:00:00")&& (Time data[time])<=TIMEVALUE("20:00:00"),"After 7:00",BLANK())

The problem with above Query is that I m getting multiple Out timing as show in green colour.

Required: A calculated column were I can get last out time as I have shown in above image,
the column in Red Colour is expected

1 ACCEPTED SOLUTION

Accepted Solutions Community Support Team

Re: Calculate Time Statisctics

Hi PrachiD,

Based on your description, you want to achieve max time in a day filtered by column [begin_date], right?

To achieve your requirement, please create a calculate column and use DAX formula below:

Expected Column =

var maxTime = CALCULATE(MAX(data[time]), FILTER(ALL(data), data[emp_id] = EARLIER(data[emp_id]) && data[begin_date] = EARLIER(data[begin_date])))

return

IF(data[time] = maxTime, data[Time_Statistics_out], BLANK())

The result is like below and you can refer to PBIX file here:

https://www.dropbox.com/s/kvfd4rwa8paenz9/For%20PrachiD.pbix?dl=0 Best Regards,

Jimmy Tao

6 REPLIES 6 Super User

Re: Calculate Time Statisctics

Hi @PrachiD

Try this Column

=
VAR max_OUT_time =
CALCULATE (
MAX ( data[time] ),
FILTER ( ALLEXCEPT ( data, data[emp_id] ), data[IN/OUT] = "O" )
)
RETURN
IF (
data[time] = max_OUT_time,
SWITCH (
TRUE (),
( data[time] ) < TIMEVALUE ( "17:00:00" ), "Before 5:00",
( data[time] ) >= TIMEVALUE ( "17:00:00" )
&& ( data[time] ) <= TIMEVALUE ( "17:30:00" ), "5:00-5:30",
( data[time] ) > TIMEVALUE ( "17:30:00" )
&& ( data[time] ) <= TIMEVALUE ( "18:00:00" ), "5:30-6:00",
( data[time] ) > TIMEVALUE ( "18:00:00" )
&& ( data[time] ) <= TIMEVALUE ( "18:30:00" ), "6:00-6:30",
( data[time] ) > TIMEVALUE ( "18:30:00" )
&& ( data[time] ) <= TIMEVALUE ( "19:30:00" ), "6:30-7:00",
( data[time] ) > TIMEVALUE ( "19:00:00" )
&& ( data[time] ) <= TIMEVALUE ( "20:00:00" ), "After 7:00",
BLANK ()
)
) Super User

Re: Calculate Time Statisctics

@PrachiD

If you are using Excel 2013 or older version...May be VAR is not supported

=
IF (
data[time]
= CALCULATE (
MAX ( data[time] ),
FILTER ( ALLEXCEPT ( data, data[emp_id] ), data[IN/OUT] = "O" )
),
SWITCH (
TRUE (),
( data[time] ) < TIMEVALUE ( "17:00:00" ), "Before 5:00",
( data[time] ) >= TIMEVALUE ( "17:00:00" )
&& ( data[time] ) <= TIMEVALUE ( "17:30:00" ), "5:00-5:30",
( data[time] ) > TIMEVALUE ( "17:30:00" )
&& ( data[time] ) <= TIMEVALUE ( "18:00:00" ), "5:30-6:00",
( data[time] ) > TIMEVALUE ( "18:00:00" )
&& ( data[time] ) <= TIMEVALUE ( "18:30:00" ), "6:00-6:30",
( data[time] ) > TIMEVALUE ( "18:30:00" )
&& ( data[time] ) <= TIMEVALUE ( "19:30:00" ), "6:30-7:00",
( data[time] ) > TIMEVALUE ( "19:00:00" )
&& ( data[time] ) <= TIMEVALUE ( "20:00:00" ), "After 7:00",
BLANK ()
)
)
PrachiD Regular Visitor

Re: Calculate Time Statisctics Using this I m getting one value for all the date
I should get the values date by date
It is Not getting filter by begin date
for some field I m not getting values

Expected:- for some field I m not getting values Super User

Re: Calculate Time Statisctics

Hi @PrachiD

I get results like this...Only the last out time is highlighted...

Do you need it in a different way

Could you share your file ?  Community Support Team

Re: Calculate Time Statisctics

Hi PrachiD,

Based on your description, you want to achieve max time in a day filtered by column [begin_date], right?

To achieve your requirement, please create a calculate column and use DAX formula below:

Expected Column =

var maxTime = CALCULATE(MAX(data[time]), FILTER(ALL(data), data[emp_id] = EARLIER(data[emp_id]) && data[begin_date] = EARLIER(data[begin_date])))

return

IF(data[time] = maxTime, data[Time_Statistics_out], BLANK())

The result is like below and you can refer to PBIX file here:

https://www.dropbox.com/s/kvfd4rwa8paenz9/For%20PrachiD.pbix?dl=0 Best Regards,

Jimmy Tao

PrachiD Regular Visitor

Re: Calculate Time Statisctics

Thank You so much Announcements Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge. Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag. Top Kudoed Authors
Users Online
Currently online: 41 members 990 guests
Recent signins:
• SimonChung_GGGG • MAJackson • christoR1 • gabrielrca • vyogi • Ritaf • Dellis81 • troystaylor • mexicobigdata • Bielite • kylerligon • drewsk • Ashley_Finney • paulocorrea 