Create a column to calculate the difference between the earliest one of a set of dates

[jobf]

I need to create a column in a table that allows calculating the difference between dates in the same column according to some conditions. For example:

Field	Application	Date
P1	1st application	01/02/2024
P1	1st application	01/04/2024
P1	2nd application	01/10/2024
P1	3rd application	01/21/2024
P2	1st application	01/03/2024
P2	1st application	01/05/2024
P2	2nd application	01/13/2024
P2	3rd application	01/25/2024
P2	3rd application	01/24/2024
P2	3rd application	01/27/2024
P3	1st application	01/07/2024
P3	2nd application	01/15/2024
P3	2nd application	01/14/2024
P3	3rd application	01/28/2024

I need a column that calculates the difference between the earsliest dates in the set of field applications with close values. For example, in field P1, I need to take the earliest date of the 2nd application and the earliest date of the 1st application and, subsequently, calculate the difference between them. I need this on all fields. The final table would look like this:

Field	Application	Date	Difference
P1	1st application	01/02/2024
P1	1st application	01/04/2024
P1	2nd application	01/10/2024	8
P1	3rd application	01/21/2024	11
P2	1st application	01/03/2024
P2	1st application	01/05/2024
P2	2nd application	01/13/2024	10
P2	3rd application	01/25/2024	11
P2	3rd application	01/24/2024	11
P2	3rd application	01/27/2024	11
P3	1st application	01/07/2024
P3	2nd application	01/15/2024	7
P3	2nd application	01/14/2024	7
P3	3rd application	01/28/2024	14

[Irwan · Accepted solution]

Hello @jobf 

please check if this accomodate your need.

This is my understanding from your explaination.

- you dont need between any 1st application difference (zero difference)

- in same field, you want to get a difference between the earliest 2nd application and the earliest 1st application. and the earliest 3rd application and the earliest 2nd application.

Create a calculated column with following DAX

Difference =

var _MinDateField =

MINX(FILTER('Table','Table'[Field]=EARLIER('Table'[Field])&&'Table'[Application]=EARLIER('Table'[Application])),'Table'[Date])

var _MinDateApplication =

IF(

    CONTAINSSTRING('Table'[Application],"2nd"),

    MINX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Field]=EARLIER('Table'[Field])&&CONTAINSSTRING('Table'[Application],"1st")),'Table'[Date]),

IF(

    CONTAINSSTRING('Table'[Application],"3rd"),

    MINX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Field]=EARLIER('Table'[Field])&&CONTAINSSTRING('Table'[Application],"2nd")),'Table'[Date])

))

Return

IF(

    CONTAINSSTRING('Table'[Application],"1st"),

    0,

    _MinDateField-_MinDateApplication

)

_MinDateField is used for finding minimum date in every field and every application.

_MinDateApplication is used for finding minimum date for 2nd application and 3rd application for every field.

Hope this will help you.

Thank you.

[v-kaiyue-msft · Accepted solution]

Hi @jobf ,

Thanks for the reply from @Irwan  and @foodd , please allow me to provide another insight: 

1. Create a calculated column to get the earliest date.

EarliestDate = 
CALCULATE(
    MIN('Table'[Date]),
    ALLEXCEPT('Table', 'Table'[Field], 'Table'[Application])
)

2. Create a calculated column to convert the Application column to a number.

NumApplication = 
VALUE(LEFT('Table'[Application], 1))

3. Create a calculated column to get the difference in dates.

Difference = 
VAR CurrentApplication = 'Table'[NumApplication]
VAR CurrentField = 'Table'[Field]
VAR CurrentDate = 'Table'[Date]
VAR PreviousApplicationDate = 
    CALCULATE(
        MIN('Table'[EarliestDate]),
        FILTER(
            'Table',
            'Table'[Field] = CurrentField &&
            'Table'[NumApplication] = CurrentApplication - 1
        )
    )
    
RETURN
    IF(
        ISBLANK(PreviousApplicationDate),
        BLANK(),
        DATEDIFF(PreviousApplicationDate, 'Table'[EarliestDate], DAY)
    )

4. You can click the small eye next to the field to hide unnecessary columns.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Irwan · Accepted solution]

Hello @jobf 

please check if this accomodate your need.

This is my understanding from your explaination.

- you dont need between any 1st application difference (zero difference)

- in same field, you want to get a difference between the earliest 2nd application and the earliest 1st application. and the earliest 3rd application and the earliest 2nd application.

Create a calculated column with following DAX

Difference =

var _MinDateField =

MINX(FILTER('Table','Table'[Field]=EARLIER('Table'[Field])&&'Table'[Application]=EARLIER('Table'[Application])),'Table'[Date])

var _MinDateApplication =

IF(

    CONTAINSSTRING('Table'[Application],"2nd"),

    MINX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Field]=EARLIER('Table'[Field])&&CONTAINSSTRING('Table'[Application],"1st")),'Table'[Date]),

IF(

    CONTAINSSTRING('Table'[Application],"3rd"),

    MINX(FILTER('Table','Table'[Date]<EARLIER('Table'[Date])&&'Table'[Field]=EARLIER('Table'[Field])&&CONTAINSSTRING('Table'[Application],"2nd")),'Table'[Date])

))

Return

IF(

    CONTAINSSTRING('Table'[Application],"1st"),

    0,

    _MinDateField-_MinDateApplication

)

_MinDateField is used for finding minimum date in every field and every application.

_MinDateApplication is used for finding minimum date for 2nd application and 3rd application for every field.

Hope this will help you.

Thank you.