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'[Date_time] < EARLIER ( 'TEST2'[Date_time] )
);
'TEST2'[Date_time]; DESC
)
RETURN
DATEDIFF ( MINX ( temp; 'TEST2'[Date_time] ); 'TEST2'[Date_time]; MINUTE )
```

Apr 26, 2022 in Power BI 1,226 views

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!

• 1,380 points

Calculate GB Using DAX in Power BI

Hi, @Vnk You can go through this regarding ...READ MORE

Using DAX calculation how to calculate monthly budget till today in power bi Desktop?

You can make use of this: MTD Budget ...READ MORE

You can simply write a few measures ...READ MORE

list reports with calculated percentage in Power BI using dax

Create a Measure called Total Revenue: Total Revenue = SUMX( ...READ MORE

Bar chart : Arranging in descending order using data from another chart

Select treemap.  Make it active.  Turn on Visual Interactions ...READ MORE

Setting Row Level Security for multiple of values

I'm trying to build up row-level security ...READ MORE

Which one should I choose Tableau or Power BI?

Hi Grim, Both the BI tools - Tableau ...READ MORE

Combine tables in Power BI

You can also achieve this using a ...READ MORE