Calculate average speed in Powerbi using DAX

Question

I have information from two cameras: the car number, the camera, and the date time. The first camera is A, while the second is B.

I need to figure out what the average speed was for automobiles that passed both cameras with a passage period of at least 5 minutes and no more than 30 minutes. The cameras are 6 kilometres apart.

Only the nearest car crossing timings for the same car were counted. For the nearest A crossing time, I couldn't find the camera B crossing time.

Diff_time = 
VAR temp =
    TOPN (
        1;
        FILTER (
            'TEST2';
            'TEST2'[license_plate] = EARLIER ( 'TEST2'[license_plate])
                && 'TEST2'[Date_time] < EARLIER ( 'TEST2'[Date_time] ) 
        );
        'TEST2'[Date_time]; DESC
    )
RETURN
    DATEDIFF ( MINX ( temp; 'TEST2'[Date_time] ); 'TEST2'[Date_time]; MINUTE )

[license_plate] is car number.

Answer 1

To calculate the average speed for automobiles that passed both cameras with a passage period of at least 5 minutes and no more than 30 minutes, considering they are 6 kilometers apart, you can follow these steps:

1. Create a calculated column in your dataset to calculate the time difference between the current row's DateTime and the nearest previous DateTime for the same license plate using the formula you provided. Name this column "Diff_time."

2. Filter the dataset to include only rows where Diff_time is between 5 and 30 minutes. This will ensure you consider only those cars that passed both cameras within the specified time range.

3. Calculate the speed for each car by dividing the distance (6 kilometers) by the time difference (in hours) between the two camera crossings. Create a new calculated column named "Speed" with the formula: Speed = 6 / (Diff_time / 60).

4. Finally, calculate the average speed by taking the average of the Speed column.

By following these steps, you can obtain the average speed for automobiles that passed both cameras within the specified time period and distance.

Preparing for the Power BI exam? Check out Power BI Course Content now!