Hi, I went though your requirements and attached spreadsheet and I understood all your requirements.
#This part was already replied by project detail. Not a question any more.
#Question 1:
#For Mileage, you mentioned calculate it by API, not sure which API it is.
#Is it be calculated by attitude and longitude from two locations? I verified by one row, and it seemed that it's not a distance.
#Can you verify this?
Question 2:
Is it a must to use Power BI to do the calculation? The reason why I'm asking is it's hard to understood and maintain. My suggestion is we can develop a tool or scripts, once you updated the three tables, and then you run a scripts which will generate/update a spreadsheet. You can check the result much easier. At the same time, in Power BI, the model will reference to the new spreadsheet, and set the query mode to direct query so that when you open a Power BI report inside or Dashboard it will automatically show the updated data.
Question 3:
What's the volume size of your locations? Since Power BI has performance issue to calculate large volume data.
All the other requirement are clear.