Count person who are joining and leaving

[third_hicana]

Hi. Seeking for your help regarding DAX.

I want to create a DAX that counts the people who join and leave the online event. I have created a visual that counts who joins in a specific hour. However, I wish to make a visual that adds the people to the count who join the online event and subtracts the number of people who leave in an hourly interval.

Below is the sample data

Name	start_time	end_time	duration (seconds)	Duration (Minutes)	Duration (Hour)
PERSON_1131	12/03/2024 6:48:55 am	12/03/2024 6:48:56 am	1	0	0
PERSON_1282	12/03/2024 6:49:10 am	12/03/2024 6:49:19 am	9	0.2	0
PERSON_1320	12/03/2024 6:49:26 am	12/03/2024 6:49:34 am	8	0.1	0
PERSON_1326	12/03/2024 6:50:57 am	12/03/2024 6:51:06 am	49	0.8	0
PERSON_1107	12/03/2024 6:53:47 am	12/03/2024 6:53:53 am	6	0.1	0
PERSON_1076	12/03/2024 6:58:49 am	12/03/2024 6:58:51 am	2	0	0
PERSON_1004	12/03/2024 6:49:12 am	12/03/2024 6:59:40 am	1028	17.1	0.3
PERSON_1217	12/03/2024 7:00:39 am	12/03/2024 7:00:44 am	5	0.1	0
PERSON_1298	12/03/2024 7:00:24 am	12/03/2024 7:00:44 am	20	0.3	0
PERSON_1217	12/03/2024 7:00:56 am	12/03/2024 7:00:59 am	3	0	0
PERSON_1426	12/03/2024 6:50:25 am	12/03/2024 7:01:00 am	5075	84.6	1.4
PERSON_1326	12/03/2024 6:51:14 am	12/03/2024 7:01:07 am	4993	83.2	1.4
PERSON_1081	12/03/2024 7:01:20 am	12/03/2024 7:01:23 am	3	0	0
PERSON_1426	12/03/2024 7:01:21 am	12/03/2024 7:01:24 am	3	0	0
PERSON_1326	12/03/2024 7:01:12 am	12/03/2024 7:01:57 am	45	0.8	0
PERSON_1130	12/03/2024 7:02:05 am	12/03/2024 7:02:08 am	3	0	0
PERSON_1006	12/03/2024 7:02:13 am	12/03/2024 7:02:14 am	1	0	0
PERSON_1068	12/03/2024 7:00:54 am	12/03/2024 7:02:18 am	164	2.7	0
PERSON_1231	12/03/2024 7:02:32 am	12/03/2024 7:02:35 am	3	0	0
PERSON_1076	12/03/2024 7:02:42 am	12/03/2024 7:02:44 am	2	0	0
PERSON_1060	12/03/2024 7:02:50 am	12/03/2024 7:02:51 am	1	0	0
PERSON_1147	12/03/2024 7:02:19 am	12/03/2024 7:02:55 am	36	0.6	0
PERSON_1210	12/03/2024 7:03:19 am	12/03/2024 7:03:20 am	1	0	0
PERSON_1564	12/03/2024 7:02:51 am	12/03/2024 7:03:26 am	75	1.2	0
PERSON_1581	12/03/2024 7:01:39 am	12/03/2024 7:03:36 am	197	3.3	0.1
PERSON_1051	12/03/2024 7:03:34 am	12/03/2024 7:03:48 am	14	0.2	0
PERSON_1074	12/03/2024 7:04:29 am	12/03/2024 7:04:31 am	2	0	0

[rajendraongole1]

Hi @third_hicana - create a calculated columns for the start and end hours in your main table.

Start Hour = HOUR([start_time])

End Hour = HOUR([end_time])

Create a separate table for the hours

HoursTable = DISTINCT(SELECTCOLUMNS('_Perso', "Hour", HOUR('_Perso'[start_time])))

create the measures to count people joining and leaving within each hour:

People Joining =

VAR SelectedHour = SELECTEDVALUE('HoursTable'[Hour])

RETURN

CALCULATE(

    COUNTROWS('_Perso'),

    FILTER('_Perso', HOUR('_Perso'[start_time]) = SelectedHour)

)

People Leaving =

VAR SelectedHour = SELECTEDVALUE('HoursTable'[Hour])

RETURN

CALCULATE(

    COUNTROWS('_Perso'),

    FILTER('_Perso', HOUR('_Perso'[end_time]) = SelectedHour)

)

Hope it works