How do you calculate km’s travelled using just Lat/long in Power Bi?

DAX DAX Calculations
1

Hi All,

I need to try and calculate kms travelled from Latitude and Longitude Coordinates but am not sure how to best do it.

Any ideas?

I’ve got some massive datasets of locations so will likely need to be able to calculat it preferably by both importing and direct query mode.

Thanks in advance for any help you can provide.

Regards

Dave

2

Interesting one. Can’t say I’'ve worked on anything like this before.

I’ve done some searching and doesn’t look super easy but likely doable.

This is the best link I’ve found as it’s excel based, but it’s still relatively complex

Here’s another one that isn’t too bad

It would just be a matter of working out the logic in excel and transferring it across to Power BI.

I may have to find some time to test this at a later point as there’s quite a bit to this.

3

Hi Sam,

Thanks for your help with this I always get stuck converting excel formulas to power bi when they start getting cell based.

I could use the formula so long as I could get a calculated column showing the exact lat/long of the exact previous coordinate of a vehicle. Only problem is all the lat/longs are generated at different times and all the vehicles are in the same column and all the latitudes are in the same column and longitudes also.

I’m thinking I’ll need to create.a calculated column filtering by vehicle and use the LastDate type dax calculation to lookup the last coordinates so I can then bring it down into the current time stamp which I could then iterate across with the km dax calculation.

Does LastDate work for date/time stamps that may have only happened a few seconds ago? I’m thinking I’m going to need to use SumX to iterate across the columns and Calculate to remove time context.

Am I thinking about this right or is there a better way?

4

Got an example of the data you can share?

There’s certainly a bit to this one. I would probably want to mock something up I think to test it.

5

GPS Sample Data.xlsx (15.6 KB)

Hi Sam,

Here you go thanks for your help with this one, once I work out how to do this I’m likely to use the same process to calculate other things.

Regards

Dave

6

David,
I enclose an excel file with the calculations in Excel. Hugely complicated to get this done in Power BI I feel.
Hence I use another tool, that based on OSM maps calculates distances (real and as the crow flies) in batch in a table, which can then be imported into Power BI, this might be the answer for you. It is fast and capable of handling big files.

The tool is called UltraMileCharter.
https://www.ultra-mileage.com/features.php

image
image781×220 9.58 KB
Afstand berekenen.xlsx (13.9 KB)
Let me know if this is the way for you and you need help.

Paul

7

Nice one, thanks Paul

8

Hi Paul and Sam,

I appreciate finding the other software Paul but i really need to work out the pattern of how to calculate this as I’ll need it not just to calculat Kms but for other calculations as well where I need to search for a similar vehicle above and make time calculations between the two.

For example I want to be able to calculate engine hours as well which is the time difference between an ignition on and off event.

Are we able to have a go at calculating kms in Power BI. I know its difficult but its a key ingredient for me to complete my day to day work. Essentially everything I do involves using kms.

Hope you can help

Regards

Dave

9

David,
I think you will require an API to either Google or Bing Maps to calculate the distance by road between two points. Even then I think they will only return a figure which represents the least distance and not the actual route taken.

The calculation of straight line distance between points is know as the Havershine Distance between Points. You can Google it to find numerous examples of how to use the calculation in excel and Power BI.
Below is probably the best Dax example I have found… however, I’d suggest and without knowing your data source, that a better option is to have this as a Power Query custom calculation step when you import your data.

Ref-To Distance = 
Var Pi = PI()
Var A1 = [Lat1]* Pi/180
Var A2 = [Long1]* Pi/180
Var B1 = [Lat2] * Pi/180
Var B2 = [Long2] * Pi/180
Var Dlon =  Abs(A2 - B2)
Var DLat =  Abs(A1 - B1)
Var R = 6371
Return
IF (
	A1 = BLANK()|| A2 = BLANK()|| B1 = BLANK() || B2 = BLANK(),
	BLANK(),
   ROUND(ACOS( SIN(A1)*SIN(B1) + COS(A1) * COS(B1) * COS(Dlon) ) * R, 2))

Hope this helps.

You may find that the equation throws an error of “Acos can not calculate the value” sometimes. I’ve found that by changing the Lat2, Long2 co-ordinates from 8 significant decimals to say 6 allows the calculation to proceed. Just be aware the problem with this is that you will not be able to calculate a 0 distance. Given that GPS is accurate to 15 metres and a change to the level of significance indicated is only 10 - 100 metres error, I don’t think this is a major concern.

10

Nice one.

11

Thanks Steve,

Really appreciate your help, will the Dax Formula above take into account having multiple vehicles in the one column? This is partly where I am getting stuck as I’m having difficulty comparing one lat/long to another with other vehicles involved.

I feel it’s definitely heading in the right direction.

I’m keen to try to bring in Bing or A Google maps Api. I think that could work well if I could get it working as we take a location whenever a vehicle turns a corner which should keep it quite accurate.

Thanks again

Dave

12

David,

I have a PBI example to show the use of the Google API key as an invoked custom function. Unfortunately this is limited to 300 addresses, (Google have recently changed their licensing terms) , proper set up of the data and requires some special error handling. (try)

(Location)=>

let

Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/geocode/json?address="&Location&"&key=&GMAPIKey")),

results = Source[results],

#"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"address_components", "formatted_address", "geometry", "place_id", "types"}, {"address_components", "formatted_address", "geometry", "place_id", "types"}),

#"Expanded geometry" = Table.ExpandRecordColumn(#"Expanded Column1", "geometry", {"bounds", "location", "location_type", "viewport"}, {"bounds", "location", "location_type", "viewport"}),

#"Expanded location" = Table.ExpandRecordColumn(#"Expanded geometry", "location", {"lat", "lng"}, {"lat", "lng"}),

#"Removed Columns" = Table.RemoveColumns(#"Expanded location",{"bounds", "address_components", "viewport", "location_type", "place_id", "types"})

in

#"Removed Columns"

Paul