Forum Discussion

jobf's avatar
jobf
Helper II
2 years ago
Solved

Difference between several dates in the same column

Hello. I need to calculate the difference between several dates in the same column. For example:

Field Date
P101/01/2024
P101/03/2024
P101/06/2024
P101/11/2024
P202/02/2024
P202/09/2024
P202/16/2024
P202/20/2024


I need to calculate the difference between a date and the date before it in the same field. If there is no previous date, the value must be 0. The final table must be like this:

Field Date Difference
P101/01/2024 0
P101/03/2024 2
P101/06/2024 3
P101/11/2024 5
P202/02/2024 0
P202/09/2024 7
P202/16/2024 7
P202/20/2024 4
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jobf 

     

    Thanks for the reply from samratpbi .

     

    jobf , you can try the following measure:

    Difference = 
VAR _Previous = MAX('Table'[Date])
VAR _PreviousRowDate = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Field]),
            'Table'[Date] < _Previous
        )
    )
RETURN
    IF(
        ISBLANK(_PreviousRowDate),
        0,
        MAX([Date]) - _PreviousRowDate
    )

     

    Output:

     

    Best Regards,
    Yulia Xu

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • samratpbi's avatar
    samratpbi
    Super User
    2 years ago

    Hi, in addition, just noticed you are resetting the calculation based on the field. Hence little modification on the previous calculation which should be like below:

    Versus previous =
    IF(
        [Field ] <> PREVIOUS([Field ]),
        0,
        IF(
            PREVIOUS([Date]) = BLANK(), 0,
        [Date] - PREVIOUS([Date])
        )
    )
     
    If this helps to resolves your problem, then please mark it as solution, Thanks
  • samratpbi's avatar
    samratpbi
    Super User
    2 years ago

    Hi, there are many ways, however I think the easiest way is to use newly introduced visual level calculation.

    Its under Home -> New Calculation.

     

    I have loaded your data and created the below calculation as New Calculation:

     

    If this helps to resolve your problem, then please mark it as solution provided. Thanks

    Dont forget to give kudos 🙂

     

    • jobf's avatar
      jobf
      Helper II
      2 years ago

      Is there a way to do this without the PREVIOUS function?

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi jobf 

         

        Thanks for the reply from samratpbi .

         

        jobf , you can try the following measure:

        Difference = 
VAR _Previous = MAX('Table'[Date])
VAR _PreviousRowDate = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Field]),
            'Table'[Date] < _Previous
        )
    )
RETURN
    IF(
        ISBLANK(_PreviousRowDate),
        0,
        MAX([Date]) - _PreviousRowDate
    )

         

        Output:

         

        Best Regards,
        Yulia Xu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.