Forum Discussion

PrachiD's avatar
PrachiD
Helper I
8 years ago
Solved

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....
General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    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

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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 ()
        )
    )
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    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's avatar
    PrachiD
    Helper I
    8 years ago





    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

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      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 ?