Difference between dates on the same line with condition.

[jobf]

Hello. I need to calculate the difference between the dates of different applications:

Field	Application	Date
P1	1st	01/01/2024
P2	1st	01/05/2024
P1	2nd	01/20/2024
P2	2nd	01/22/2024
P1	3rd	02/10/2024
P2	3rd	02/11/2024
P1	1st	02/15/2024
P2	1st	02/15/2024
P1	2nd	02/27/2024
P2	2nd	02/28/2024
P1	3rd	03/10/2024
P2	3rd	03/11/2024
P1	1st	03/14/2024
P2	1st	03/16/2024
P1	2nd	03/20/2024
P2	2nd	03/24/2024
P1	3rd	04/02/2024
P2	3rd	04/05/2024

I need a column or measure to calculate the difference in date averages from different applications of the same Field. For example, the difference between the average dates of the 2nd Application and 1st Application of Field P1, the difference between the average dates of the 3rd Application and 2nd Application of Field P1, and so on for all Fields.

[Ashish_Mathur · Accepted solution]

Hi,

Write this calculated column formula

Column = if(ISBLANK(CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Field]=EARLIER(Data[Field])&&Data[Date]<EARLIER(Data[Date])))),BLANK(),1*(Data[Date]-CALCULATE(MAX(Data[Date]),FILTER(Data,Data[Field]=EARLIER(Data[Field])&&Data[Date]<EARLIER(Data[Date])))))

hope this helps.