Last country visited timeline logic

[sse]

Hi! I am struggling to figure this out and would appreciate some help. 

I am trying to make a PowerBi report that shows "Which country did people vist before they came to their current country". The user of the dashboard would typically select a country and see the previous country visited. The end product should be able to show all previous countries visited but as a starter the previous country would suffice. 

I can easily build this for each person, but I am struggling to create an aggregated view. E.g., when I select Germany I can see that 500, 300 and 250 people visited France, UK and USA before visiting Germany. 

Any tips? 

[cs_skit · Accepted solution]

this was a pretty tough one for me thanks for that was fun

created calculated column

VisitOrder = COUNTROWS
(
FILTER(countryvisits;EARLIER(countryvisits[Person])= countryvisits[Person] && EARLIER(countryvisits[DateCol])>countryvisits[DateCol])
)
+1

created second calculated column

LastCountry = CONCATENATEX
(
FILTER(countryvisits;EARLIER(countryvisits[Person])=countryvisits[Person]&&EARLIER(countryvisits[VisitOrder])-1 = countryvisits[VisitOrder])
;countryvisits[Country]
)

result

[v-shex-msft · Accepted solution]

Hi @sse,

You can try to use below formula to get the result table if it suitable for your requirement.

Table formula:

Person Country Records = 
ADDCOLUMNS(
    SUMMARIZE(Table,[Person],"Country",LASTNONBLANK(Table[Country],[Country]),"Last Timestamp",LASTTimestamp(Table[Timestamp])),
    "Previous",LOOKUPVALUE(Table[Country],[Person],[Person],[Timestamp],MAXX(FILTER(ALL(Table),[Person]=EARLIER([Person])&&[Timestamp]<EARLIER([Last Timestamp])),[Timestamp]))
    )

Regards,

Xiaoxin Sheng

[cs_skit · Accepted solution]

this was a pretty tough one for me thanks for that was fun

created calculated column

VisitOrder = COUNTROWS
(
FILTER(countryvisits;EARLIER(countryvisits[Person])= countryvisits[Person] && EARLIER(countryvisits[DateCol])>countryvisits[DateCol])
)
+1

created second calculated column

LastCountry = CONCATENATEX
(
FILTER(countryvisits;EARLIER(countryvisits[Person])=countryvisits[Person]&&EARLIER(countryvisits[VisitOrder])-1 = countryvisits[VisitOrder])
;countryvisits[Country]
)

result