Forum Discussion

jobf's avatar
jobf
Helper II
2 years ago
Solved

Difference between dates in the same column

Hello. I need to create a column or measure that can calculate the difference between two dates in the same column, based on lines from other columns. For example:

 

Field  Input Application  Date
P1  INPUT1  1st01/01/2024
P2  INPUT1  1st01/05/2024
P1  INPUT1  2nd01/20/2024
P2  INPUT1  2nd01/22/2024
P1  INPUT1  3rd02/10/2024
P2  INPUT1  3rd02/11/2024
P1  INPUT2  1st02/15/2024
P2  INPUT2  1st02/15/2024
P1  INPUT2  2nd02/27/2024
P2  INPUT2  2nd02/28/2024
P1  INPUT2  3rd03/10/2024
P2  INPUT2  3rd03/11/2024
P1  INPUT3  1st03/14/2024
P2  INPUT3  1st03/16/2024
P1  INPUT3  2nd03/20/2024
P2  INPUT3  2nd03/24/2024
P1  INPUT3  3rd04/02/2024

P2

  INPUT3  3rd

04/05/2024

 

Then I would need, for example, the difference between the date of the 2nd application of INPUT3 on field P1 and the date of the 1st application of INPUT3 on field P1.  I needed to create a column or measure that calculated the difference between all the dates (days) of the same application of the same INPUT in the same field.

Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi jobf 

     

    You can add a calculated column with this formula

    Duration = 
var _currentDate = 'Table (2)'[Date]
var _previousDate = CALCULATE(MAX('Table (2)'[Date]),ALLEXCEPT('Table (2)','Table (2)'[Field],'Table (2)'[  Input]),'Table (2)'[Date]<_currentDate)
return
DATEDIFF(_previousDate,_currentDate,DAY)

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

5 Replies

  • NaveenGandhi's avatar
    NaveenGandhi
    Memorable Member
    2 years ago

    Hi 

    The requirement is quite confusing, But try below soltution. 

    Create a column using below DAX

    Key = 'Table'[Field]&'Table'[  Input]&'Table'[ Application  ]

    Then create another column
    Diff =

    Var Date1 = CALCULATE(MAX('Table'[Date]),ALL('Table'),'Table'[Key]<EARLIER('Table'[Key]))

    RETURN DATEDIFF(Date1,'Table'[Date],DAY)



    Let me know if this is what you are expecting or share a little bit more details with sample output.

     If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!


    • jobf's avatar
      jobf
      Helper II
      2 years ago

      It's kind of hard to understand, but I'll try to explain. In a field, several inputs will be applied, and these inputs will be applied more than once. I needed to calculate, somehow, the difference between these applications of this input in this specific field. In this case, the difference between the 2nd and 1st and the difference between the 3rd and 2nd.

      • NaveenGandhi's avatar
        NaveenGandhi
        Memorable Member
        2 years ago

        It would be great if you can share some pictures and explain it in detail, The details you have provided is definelty for enough to resolve the problem.

        Regards,

        Naveen

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi jobf 

     

    You can add a calculated column with this formula

    Duration = 
var _currentDate = 'Table (2)'[Date]
var _previousDate = CALCULATE(MAX('Table (2)'[Date]),ALLEXCEPT('Table (2)','Table (2)'[Field],'Table (2)'[  Input]),'Table (2)'[Date]<_currentDate)
return
DATEDIFF(_previousDate,_currentDate,DAY)

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

    • jobf's avatar
      jobf
      Helper II
      2 years ago

      Overall, the code worked, but an error occurred in ALLEXCEPT. "The ALLEXCEPT function expects a table reference expression for argument '4', but a string or numeric expression was used."