Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Nb days between 2 dates

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   ...
Need Help
Show and Tell
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    OMG!!

    Successfull!!!

     

    Thanks a lot!!