Calculate average speed in Powerbi using DAX

0 votes

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.

image

Apr 26, 2022 in Power BI by Neha
• 9,060 points
912 views

1 answer to this question.

0 votes

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! 

answered Jun 20, 2023 by Neelam
• 1,180 points

Related Questions In Power BI

0 votes
1 answer

Calculate GB Using DAX in Power BI

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

answered Jun 30, 2020 in Power BI by Gitika
• 65,910 points
4,017 views
0 votes
1 answer

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

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

answered Sep 24, 2020 in Power BI by Gitika
• 65,910 points
1,515 views
0 votes
1 answer

Left Outer Join using DAX in PowerBI.

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

answered Sep 24, 2020 in Power BI by Gitika
• 65,910 points
1,361 views
0 votes
1 answer

list reports with calculated percentage in Power BI using dax

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

answered Oct 22, 2018 in Power BI by Hannah
• 18,570 points
1,333 views
0 votes
1 answer

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

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

answered Feb 8, 2019 in Power BI by Upasana
• 8,620 points
457 views
0 votes
0 answers

Setting Row Level Security for multiple of values

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

Apr 26, 2022 in Tableau by Neha
• 9,060 points
813 views
0 votes
1 answer

Which one should I choose Tableau or Power BI?

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

answered Apr 6, 2018 in Tableau by ghost
• 1,790 points
629 views
+3 votes
2 answers

Combine tables in Power BI

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

answered Oct 5, 2018 in Power BI by lina
• 8,220 points

edited Oct 11, 2018 by Kalgi 2,080 views
0 votes
1 answer

Categorised data in PowerBi

If you're experiencing performance issues when setting ...READ MORE

answered Jun 20, 2023 in Power BI by anonymous
• 1,180 points
574 views
0 votes
1 answer

How to Split Columns in Power BI?

Splitting columns in Power BI using the ...READ MORE

answered Jan 6 in Power BI by anonymous
• 1,180 points
560 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP