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 P1 01/01/2024 P1 01/03/2024 P1 01/06/2024 P1 01/11/2024 P2 02/02/...
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.

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.