Difference between set of dates on the same column

[jobf]

Hello. I need to calculate the difference between the dates of different applications:

Field	Application	Date
P1	1st	01/01/2024
P2	1st	01/05/2024
P1	2nd	01/20/2024
P2	2nd	01/22/2024
P1	3rd	02/10/2024
P2	3rd	02/11/2024
P1	1st	02/15/2024
P2	1st	02/15/2024
P1	2nd	02/27/2024
P2	2nd	02/28/2024
P1	3rd	03/10/2024
P2	3rd	03/11/2024
P1	1st	03/14/2024
P2	1st	03/16/2024
P1	2nd	03/20/2024
P2	2nd	03/24/2024
P1	3rd	04/02/2024
P2	3rd	04/05/2024

For example, I need to take the dates of the 2nd application of P1, select the oldest of them, and subtract the oldest date of the 1st application of P1. Therefore, I need to take the dates of the 3rd application of P1, select the oldest of them, and subtract by the oldest date of the 2nd application of P1. In the truth table, there will be more types of application, such as 4th and 5th. And so on with all Fields.

[v-yangliu-msft · Accepted solution]

Thanks for the reply from @vigneshba , please allow me to provide another insight: 

Hi  @jobf ,

Here are the steps you can follow：

1. Create calculated column.

Index =
VALUE(
LEFT(
    'Table'[Application],LEN('Table'[Application])-2))

2. Create measure.

Measure =
var _current=
MAXX(
    FILTER(ALL('Table'),
    'Table'[Field]=MAX('Table'[Field])&&'Table'[Index]=MAX('Table'[Index])),[Date])
var _last=
MAXX(
    FILTER(ALL('Table'),
    'Table'[Field]=MAX('Table'[Field])&&'Table'[Index]=MAX('Table'[Index])-1),[Date])
return
DATEDIFF(
    _last,_current,DAY)

3. Result:

Best Regards,

Liu Yang

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

[jobf]

Hello. Thanks, your solution worked. I just have one question. In the true table, for example, there are some moments where there is a 1st application and 3rd application, but there is no 2nd application. I would like to know if there is a possibility of changing the measure so that, if the previous application (-1) does not exist, it subtracts from the column that came before it (-2).

[jobf]

There was an error. DAX comparison operations do not support comparing values of type Text with values of type Number. Consider using the VALUE or FORMAT function to convert one of the values.

[vigneshba]

Hi @jobf - I hope you are expecting below result

Please follow below steps to achieve this.

I have just loaded the same dataset in Power BI and created a reference in Power Query and grouped this table with Field and Application to get Oldest Date in Power BI. I have created reference here not to disturb the Original Data but we can apply group by directly on the Original Data

You have to create 1 Calculated Measurement which I have created as Prior Oldest Date here to get the prior date for every group (Field and Application) and first item it will be blank as there are no prior dates available

Prior Oldest Date = CALCULATE(MAX('Use Case'[Oldest Date]), FILTER(ALLEXCEPT('Use Case', 'Use Case'[Field]), 'Use Case'[Oldest Date]<EARLIER('Use Case'[Oldest Date])))
You have to create one more Calculated Dimension to find the Date Difference between these 2 dates now

Date Difference = DATEDIFF('Use Case'[Prior Oldest Date], 'Use Case'[Oldest Date], DAY)

Hope this helps you to do date difference between the dates in the same column

[vigneshba]

@jobf - Have you tried this approach?