Dax help on to calculate login logout min in each hour interval

[PowerBigginer]

Hi Team,
I want to calculate difference in minutes for each one hour. 

If an employee logs in at 8:30 and logs out at 11:30, we want to calculate the time spent for each hourly interval. Here's how it should look:

For the 8-9 interval: 30 minutes
For the 9-10 interval: 60 minutes
For the 10-11 interval: 60 minutes
For the 11-12 interval: 30 minutes (because the logout time is at 11:30)

Sample data

Emp Id	Emp Name	Job Title	Log In Time	Log Out Time	Dif in min	HourlyInterval
123	Hari	Manager	5/2/2024 8:25	5/2/2024 11:00	155	08-09
123	Hari	Manager	5/2/2024 8:20	5/2/2024 8:25	5	08-09
123	Hari	Manager	5/2/2024 11:34	5/2/2024 11:49	15	11-12
123	Hari	Manager	5/2/2024 11:17	5/2/2024 11:32	15	11-12

[danextian]

Hi @PowerBigginer 

For record #1:

• why is the interval 8-9 when the range is from 8-11?
• how many rows do you expect to see as the result?

Please provide expected result using the sample data.

[PowerBigginer]

Business requirement is to track how many hours he was in online. And I am not expecting in the row I am adding this in matrix visual 

[danextian]

I asked because you mentioned that "we want to calculate the time spent for each hourly interval" which I assumed you wanted to show each interval as a row.

[PowerBigginer]

I tried with this dax but not getting expected result 

HourlyDifference =

VAR LoggedInHour = HOUR(MAX(Tbl_Login[Logged In Time]))  -- Get the hour of login

VAR LoggedOutHour = HOUR(MAX(Tbl_Login[Logged Out Time]))  -- Get the hour of logout

VAR LoggedOutMinute = MINUTE(MAX(Tbl_Login[Logged Out Time]))  -- Get the minute of logout

VAR LoggedInMinute = MINUTE(MAX(Tbl_Login[Logged In Time]))  -- Get the minute of login

RETURN

    IF(

        LoggedInHour = LoggedOutHour,  -- If login and logout are in the same hour

        LoggedOutMinute - LoggedInMinute,  -- Calculate the difference in minutes

        IF(

            LoggedOutHour = LoggedInHour + 1,  -- If logout is in the next hour

            60 - LoggedInMinute,  -- Calculate the remaining minutes in the login hour

            IF(

                LoggedInHour < 12,  -- If login hour is before 12

                60,  -- Full hour for the login hour

                IF(

                    LoggedOutHour > 0,  -- If logout hour is after 0 (midnight)

                    LoggedOutMinute,  -- Remaining minutes until logout

                    0  -- Default value if logout is not after midnight

                )

            )

        )

    )