Difference between several dates in the same column

[jobf]

Hello. I need to calculate the difference between several dates in the same column. For example:

Field	Date
P1	01/01/2024
P1	01/03/2024
P1	01/06/2024
P1	01/11/2024
P2	02/02/2024
P2	02/09/2024
P2	02/16/2024
P2	02/20/2024

I need to calculate the difference between a date and the date before it in the same field. If there is no previous date, the value must be 0. The final table must be like this:

Field	Date	Difference
P1	01/01/2024	0
P1	01/03/2024	2
P1	01/06/2024	3
P1	01/11/2024	5
P2	02/02/2024	0
P2	02/09/2024	7
P2	02/16/2024	7
P2	02/20/2024	4

[samratpbi]

Hi, in addition, just noticed you are resetting the calculation based on the field. Hence little modification on the previous calculation which should be like below:

Versus previous =

IF(

    [Field ] <> PREVIOUS([Field ]),

    0,

    IF(

        PREVIOUS([Date]) = BLANK(), 0,

    [Date] - PREVIOUS([Date])

    )

)

If this helps to resolves your problem, then please mark it as solution, Thanks

[samratpbi]

Hi, there are many ways, however I think the easiest way is to use newly introduced visual level calculation.

Its under Home -> New Calculation.

I have loaded your data and created the below calculation as New Calculation:

If this helps to resolve your problem, then please mark it as solution provided. Thanks

Dont forget to give kudos 🙂

[jobf]

Is there a way to do this without the PREVIOUS function?

[v-xuxinyi-msft · Accepted solution]

Hi @jobf 

Thanks for the reply from @samratpbi .

@jobf , you can try the following measure:

Difference = 
VAR _Previous = MAX('Table'[Date])
VAR _PreviousRowDate = 
    CALCULATE(
        MAX('Table'[Date]),
        FILTER(
            ALLEXCEPT('Table', 'Table'[Field]),
            'Table'[Date] < _Previous
        )
    )
RETURN
    IF(
        ISBLANK(_PreviousRowDate),
        0,
        MAX([Date]) - _PreviousRowDate
    )

Output:

Best Regards,
Yulia Xu

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