Difference between dates in the same column

[jobf]

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	1st	01/01/2024
P2	INPUT1	1st	01/05/2024
P1	INPUT1	2nd	01/20/2024
P2	INPUT1	2nd	01/22/2024
P1	INPUT1	3rd	02/10/2024
P2	INPUT1	3rd	02/11/2024
P1	INPUT2	1st	02/15/2024
P2	INPUT2	1st	02/15/2024
P1	INPUT2	2nd	02/27/2024
P2	INPUT2	2nd	02/28/2024
P1	INPUT2	3rd	03/10/2024
P2	INPUT2	3rd	03/11/2024
P1	INPUT3	1st	03/14/2024
P2	INPUT3	1st	03/16/2024
P1	INPUT3	2nd	03/20/2024
P2	INPUT3	2nd	03/24/2024
P1	INPUT3	3rd	04/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.

[Anonymous · Accepted solution]

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]

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

[NaveenGandhi]

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]

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]

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