Forum Discussion
Origin - Destiny
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.
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
1 Reply
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