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          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

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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      vanessafvg
      Community Champion
      9 years ago

      Anonymous

       

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

      • 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.