topic Re: Create a column to calculate the difference between the earliest one of a set of dates in DAX Commands and Tips

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

jobf — Tue, 09 Jul 2024 19:34:43 GMT

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

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

Irwan — Tue, 09 Jul 2024 23:38:50 GMT

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"),

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

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

Anonymous — Wed, 10 Jul 2024 02:35:37 GMT

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.