Nb days between 2 dates

[Anonymous]

Hi everybody,

I did not find the issue, sorry il it exists.

I got a problem to find the delay between 2 dates with one id.

Ex :

id         date

1          01/05/2017

2          02/05/2017

3          02/05/2017

2          04/05/2017

3          05/05/2017

I would like "null" and 2 days for n°2 and "null" and 3 days for n°3 (and null for n°1)

I would like to have the average too, so 1 days for n°2 and 1,5 for n°3 (and 0 for n°1)

Am i clear or not?

Thank you for all,

Ben

[Anonymous]

Something more,

See what I would like :

id         date               result

1          01/05/2017    null

2          02/05/2017    null

3          02/05/2017    null

2          04/05/2017    2

3          05/05/2017    3

...

[vanessafvg]

@Anonymous

ben what are you trying to do, probably easier if you explain the business requirement, ie whats your objective

[Anonymous]

Ok, I try to explain the purpose...

with "tx" a percent (0,25 -> a garbage/container 25% fulfilled, need to be dumped),

the date is the date we dump the garbage/container,

the id is the reference of the container.

id         tx       date               result      Time

1          0,25    01/05/2017    null        /

2          0,5      02/05/2017    null        /

3          0,25    02/05/2017    null        /

2          1         04/05/2017    2           2

3          0,5      05/05/2017    3           6

...

The "Time" is the difference between the two last dates of the id divided by the tx.

It will be better with many "Time" to have one average time with each id.

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

I reproduce your scenario and get expected results.

Please create the calculated coulmn using the formula below step by step.

Rank = RANKX(FILTER(Test,Test[id]=EARLIER(Test[id])),Test[date],,ASC)

Result = IF(ISBLANK(LOOKUPVALUE(Test[date],Test[id],Test[id],Test[Rank],Test[Rank]-1)),BLANK(),DATEDIFF(LOOKUPVALUE(Test[date],Test[id],Test[id],Test[Rank],Test[Rank]-1),Test[date],DAY))

Time = Test[Result]/Test[tx]

Desired result
Best Regards,
Angelia

[Anonymous]

OMG!!

Successfull!!!

Thanks a lot!!