Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Qwerty2002
Regular Visitor

Calculate time spent in each country

Hi everyone!

 

I would need to calculate amount of time which salesperson spent in each country while traveling between different cities. Hardest part is that salesperson can visit one country several times. Idea is to enumerate each visit and then calculate time spent in each country. Any idea how this can be done?

DateCityCountry
2/10/2019AUS
2/11/2019BUS
2/14/2019AUS
2/17/2019AGB
2/20/2019BGB
2/25/2019CGB
2/27/2019BGB
2/28/2019AUS
3/1/2019BUS
3/3/2019CUS
3/5/2019DUS
3/6/2019BUS
3/7/2019DUS
3/8/2019AGB
3/9/2019BGB
3/14/2019CGB
3/15/2019AGB
3/17/2019BGB
5 REPLIES 5
Ashish_Mathur
Super User
Super User

Hi,

Please show the exact expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Ashish, hi

 

Result should be as follows:

US 1 (first occurence of country) - 7 (2/10->2/17)

GB 1 - 11 (2/17->2/28)

US 2 - 8 (2/28 -> 3/08)

...

An so on

 

Thanks

Anyone can help? 😞

SteveCampbell
Memorable Member
Memorable Member

Can you confirm the calculation?

You ask for how long in each country, but there is single entries for date. A, for eaxmple:

DateCityCountry
2/10/2019AUS
2/14/2019AUS
2/17/2019AGB
2/28/2019AUS

 

are we to assume A was in the US from 2/10 - 2/17, and in GB 2/17-2/18 ?

 

Or is the date counted as one day, so in the example 3 days in US and 1 day in GB



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



Steve, hi

 

You are correct. We assume that A in US was 2/10 - 2/17; GB - GB 2/17-2/28. We also assume that city A in GB is different from city A in US

 

Thanks

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.