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

Calculate Time Statisctics

Capture.PNG

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
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

 1.PNG

Best Regards,

Jimmy Tao

View solution in original post

6 REPLIES 6
Super User
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
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
Regular Visitor

Re: Calculate Time Statisctics

Capture2.PNG



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:-
Capture3.PNG

for some field I m not getting values

Super User
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 ?

 

Time Statistics.png

Community Support Team
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

 1.PNG

Best Regards,

Jimmy Tao

View solution in original post

PrachiD Regular Visitor
Regular Visitor

Re: Calculate Time Statisctics

Thank You so much Smiley Happy

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 41 members 990 guests
Please welcome our newest community members: