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

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.

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.