Calculate operation hours / detections per hour per day

[_D_D_]

Hello,

i have the following data which it cannot be changed in power query, all has to be done with DAX.

it contains scans, each scan has a date/time, i wish to calculate the number of scans per hour base upon the first scan of that day and the last scan of that day (operation hours).

Skid	DetectionTime	Skid_DetectionTime	DetectionId	ParkingRightTypeText	VerificationResult	Date	DetectionTime (bins)	dagdeel	Time
425439	3/07/2023 11:45	20230703	4,8608E+16		FO|EXCEPTION|TEMPORARY_ZONE	monday 3 july 2023	3/07/2023 11:40	D1	11:40:00
425470	3/07/2023 11:46	20230703	4,86081E+16		FINED|NOTPAID	monday 3 july 2023	3/07/2023 11:40	D1	11:40:00
425498	3/07/2023 11:49	20230703	4,86081E+16		FO|EXCEPTION|HANDICAP	monday 3 july 2023	3/07/2023 11:40	D1	11:40:00
426436	3/07/2023 14:25	20230703	4,86087E+16		FINED|NOTPAID	monday 3 july 2023	3/07/2023 14:20	D2	14:20:00
426439	3/07/2023 14:25	20230703	4,86087E+16		FINED|NOTPAID	monday 3 july 2023	3/07/2023 14:20	D2	14:20:00
426441	3/07/2023 14:25	20230703	4,86087E+16		FINED|NOTPAID	monday 3 july 2023	3/07/2023 14:20	D2	14:20:00
426463	3/07/2023 14:26	20230703	4,86087E+16		FINED|NOTPAID	monday 3 july 2023	3/07/2023 14:20	D2	14:20:00
426476	3/07/2023 14:28	20230703	4,86087E+16		FO|EXCEPTION|HANDICAP	monday 3 july 2023	3/07/2023 14:20	D2	14:20:00
426480	3/07/2023 14:28	20230703	4,86087E+16		FO|EXCEPTION|HANDICAP	monday 3 july 2023	3/07/2023 14:20	D2	14:20:00
426489	3/07/2023 14:29	20230703	4,86087E+16		FO|EXCEPTION|HANDICAP	monday 3 july 2023	3/07/2023 14:20	D2	14:20:00
426540	3/07/2023 14:31	20230703	4,86087E+16		FO|EXCEPTION|LOAD_UNLOAD	monday 3 july 2023	3/07/2023 14:30	D2	14:30:00
426577	4/07/2023 7:24	20230704	4,86127E+16		FINED|NOTPAID	tuesday 4 july 2023	4/07/2023 7:20	D1	7:20:00
426580	4/07/2023 7:24	20230704	4,86127E+16		FINED|NOTPAID	tuesday 4 july 2023	4/07/2023 7:20	D1	7:20:00
426583	4/07/2023 7:24	20230704	4,86127E+16		FO|EXCEPTION|EMERGENCY_SERVICES	tuesday 4 july 2023	4/07/2023 7:20	D1	7:20:00
426606	4/07/2023 7:25	20230704	4,86127E+16		FO|EXCEPTION|HANDICAP	tuesday 4 july 2023	4/07/2023 7:20	D1	7:20:00
426637	4/07/2023 7:26	20230704	4,86127E+16		FINED|NOTPAID	tuesday 4 july 2023	4/07/2023 7:20	D1	7:20:00
426644	4/07/2023 7:26	20230704	4,86127E+16		FINED|NOTPAID	tuesday 4 july 2023	4/07/2023 7:20	D1	7:20:00
426680	4/07/2023 7:27	20230704	4,86127E+16		FINED|NOTPAID	tuesday 4 july 2023	4/07/2023 7:20	D1	7:20:00
429267	4/07/2023 13:36	20230704	4,86141E+16		FO|EXCEPTION|HANDICAP	tuesday 4 july 2023	4/07/2023 13:30	D1	13:30:00
429271	4/07/2023 13:36	20230704	4,86141E+16		FO|EXCEPTION|HANDICAP	tuesday 4 july 2023	4/07/2023 13:30	D1	13:30:00
429272	4/07/2023 13:36	20230704	4,86141E+16		FO|EXCEPTION|HANDICAP	tuesday 4 july 2023	4/07/2023 13:30	D1	13:30:00
429576	4/07/2023 14:31	20230704	4,86144E+16		InvalidLicensePlate	tuesday 4 july 2023	4/07/2023 14:30	D2	14:30:00
429987	5/07/2023 7:22	20230705	4,86183E+16		FO|EXCEPTION|HANDICAP	wednesday 5 july 2023	5/07/2023 7:20	D1	7:20:00
430012	5/07/2023 7:22	20230705	4,86183E+16		FINED|NOTPAID	wednesday 5 july 2023	5/07/2023 7:20	D1	7:20:00
430014	5/07/2023 7:22	20230705	4,86183E+16		FINED|NOTPAID	wednesday 5 july 2023	5/07/2023 7:20	D1	7:20:00

thanks in advance

[bhanu_gautam · Accepted solution]

@_D_D_ , You can try using below mentioned DAX  

ScansPerHour =
VAR MinTime =
CALCULATE (
MIN ( 'Table'[SkidDetectionTime] ),
ALLEXCEPT ( 'Table', 'Table'[DetectionId], 'Table'[ParkingRightTypeTextVerificationResultDate] )
)
VAR MaxTime =
CALCULATE (
MAX ( 'Table'[SkidDetectionTime] ),
ALLEXCEPT ( 'Table', 'Table'[DetectionId], 'Table'[ParkingRightTypeTextVerificationResultDate] )
)
VAR TotalScans =
CALCULATE (
COUNTROWS ( 'Table' ),
ALLEXCEPT ( 'Table', 'Table'[DetectionId], 'Table'[ParkingRightTypeTextVerificationResultDate] )
)
VAR TotalHours =
HOUR ( MaxTime - MinTime ) + IF ( MINUTE ( MaxTime - MinTime ) > 0, 1, 0 )
RETURN
DIVIDE ( TotalScans, TotalHours )