Origin - Destiny

[Anonymous]

Hi everyone. I'm new in Power BI and I'm trying to do some kind of analysis basis on the origin of a client and the destiny. This is about a parking lot. 

This is an example: 

Date	Hour	Phone	Duration (minutes)	Payment	Client
01/01/2020	6:00 am	222	60	10	A
01/01/2020	1:00 pm	222	45	15	B
02/01/2020	3:00 pm	221	140	60	C
02/01/2020	4:00 pm	223	200	70	A

I want for example that a user (phone) visited client A and then he visited client B, so what do I do to make the client A a origin and B a destiny?. Is that possible? 

I want to do for every Client that I have. 

Thank you. 

[v-lili6-msft · Accepted solution]

hi @Anonymous 

For your case, you could try this way as below:

Step1:

Add a datetime column that combine date column and hour column

Datetime = 'Table'[Date]+'Table'[Hour ]

Step2:

For your case, if a user (phone) that only have one record in one day, how do you define it?

If you want it as a origin

Just use this formula to create a column:

Column = 
var _firstdatetime=CALCULATE(MIN('Table'[Datetime]),FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Phone]=EARLIER('Table'[Phone])))
var _lastdatetime=CALCULATE(MAX('Table'[Datetime]),FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Phone]=EARLIER('Table'[Phone])))
return
IF('Table'[Datetime]=_firstdatetime,"origin",IF('Table'[Datetime]=_lastdatetime,"destiny"))

or if you want it as blank or other value, just by this logic:

Column 2 = 
var _firstdatetime=CALCULATE(MIN('Table'[Datetime]),FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Phone]=EARLIER('Table'[Phone])))
var _lastdatetime=CALCULATE(MAX('Table'[Datetime]),FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Phone]=EARLIER('Table'[Phone])))
return
IF(_firstdatetime=_lastdatetime,BLANK(), IF('Table'[Datetime]=_firstdatetime,"origin",IF('Table'[Datetime]=_lastdatetime,"destiny")))

Result:

and here is sample pbix file, please try it.

Regards,

Lin