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

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