Destination from single departure

[ybatistamayo]

I have a table with the purchase information of the air ticket and I would like to obtain a column with the destination of the agreement with the following conditions
1- If the difference between the minimum date and the next date of the "datefligth" column is less than 24 hours, then the destination is the value of the third row of the "Origin" column. This for each ticket in the "IDTICKET" column"

https://drive.google.com/file/d/1MiEcw7TeZCRLygYl_l47gWgCP8y4c2ls/view

this is what i hope to get

[v-kelly-msft]

Hi @ybatistamayo ，

I'm a little confused about the logic,you said "the difference between date and the next previous date of the "datefligth" column",can you advise me whether the calculation is executed in the "dateflight" column,does it mean difference= the next date of "dateflight" minus the current date,then convert it into hours?Such as shown below?

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[ybatistamayo]

Thanks for answering. I think what I am looking for looks better if you analyze the excel table. What I want to return is a destination making the calculation of difference of dates, between the minimum date and the next date of the "DateFlight" column. This for each ticket.

If the difference between both dates is less than 24 hours, return the value of the 3rd row of the "Origin" column if it is greater than 24 then the value of the second row of the "Origin" column.  I remind you, for each ticket

[v-kelly-msft]

Hi @ybatistamayo ，

It would be appreciated if you could advise me more details as it seems the logic doesnt match the output you showed in the original post:

Take ID125859031040 as an example: the minumun date is 18/8/27,the difference between the second date and the munimun date is 24 hrs,it returns the 3rd value of the departure column,and the difference between the next dates and the munimun are all above 24 hrs,they all  return the 3rd value of the departure column,so I have a conclusion,that is if difference is greater than 24hrs,it will return the 3rd value of the departure column.But if we see ID 99625858886610, the minumun date is 18/8/27,the difference between the second date and the munimun is also 24 hrs,BUT it returns  the 2nd value of the departure column,and all the other dates under the ID return the same value,can you explain why?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[darentengdrake]

Hi @ybatistamayo 

This is the approach I would use.

First, copy the column DATEFLIGTH and DESTINATION and put them in a new table. Then, create a new conditional column in the table with all the data, calculating the hours between the date and DATEFLIGHT, with the condition if it is less than 24, add 1 day, if it's more than 24 hours, keep the same value. Then, you can use LOOKUPVALUE/VLOOKUP to look up by the date on the new table you created in the first step. (This is assuming DATEFLIGHT is unique)

Daren

[Greg_Deckler]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[ybatistamayo]

Very good entry, but what I need is to apply the difference of dates between two rows of the same column, that is, to access the  outer row contexts