Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
PrachiD
Helper I
Helper I

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
v-yuta-msft
Community Support
Community Support

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
v-yuta-msft
Community Support
Community Support

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

Thank You so much Smiley Happy

Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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

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


Regards
Zubair

Please try my custom visuals

@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 ()
    )
)

Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.