Multiple differences between values ​​in the same column

[jobf]

I need to calculate the difference between dates in the same column. For example:

Field	Input	Date
P1	Enzow	2024/01/24
P1	Plut	2024/02/25
P1	Plut	2024/02/19
P1	Plut	2024/02/10
P1	Enzow	2024/01/20
P2	Plut	2024/02/10
P2	Enzow	2024/01/20
P2	Enzow	2024/01/12
P2	Enzow	2024/01/10

I need to create a column that calculates the difference between the current date and the previous date in days, based on the Field and Input column. The final table would look something like this:

Field	Input	Date	Difference
P1	Enzow	2024/01/24	4
P1	Plut	2024/02/25	6
P1	Plut	2024/02/19	9
P1	Plut	2024/02/10
P1	Enzow	2024/01/20
P2	Plut	2024/02/10
P2	Enzow	2024/01/20	8
P2	Enzow	2024/01/12	2
P2	Enzow	2024/01/10

If there is no previous date, there should not be a value.

[Irwan · Accepted solution]

hello @jobf 

please check if this accomodate your need.

Difference =
var _MinDate = MINX(FILTER('Table','Table'[Field]=EARLIER('Table'[Field])&&'Table'[Input]=EARLIER('Table'[Input])),'Table'[Date])
var _PreviousDate = 
CALCULATE(
    MAX('Table'[Date]),
    ALL('Table'),
    OFFSET(-1,ORDERBY('Table'[Date]),PARTITIONBY('Table'[Field],'Table'[Input]))
)
Return
IF(
    'Table'[Date]=_MinDate,
    BLANK(),
    'Table'[Date]-_PreviousDate
)

Hope this will help you.

Thank you.

[jobf]

Is there a way to put a zero in place of this empty space in earliest dates?

[Irwan]

hello @jobf 

use 0 instead of BLANK for true value in if statement.

Hope this will help you.

Thank you.