cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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

2 ACCEPTED SOLUTIONS
Solution Sage

Hello @jobf

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.

Thank you.
Community Support

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.

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.

2 REPLIES 2
Community Support

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.

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.

Solution Sage

Hello @jobf

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.

Thank you.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors