Calculate difference between two date values in one row while referencing another row

[Beaty]

I'm still fairly new to Power BI.

I am trying to get the time difference for the P21 column for objects with same version(OV_VERSIONPART is the version, OV_OBJECTPART is the document ID). I have tried using datediff and lookupvalue but I keep getting negative values. Not sure what to do anymore. Any help will be appreciated.    

[v-yulgu-msft · Accepted solution]

Hi @Beaty,

Do you want to calculate the difference between two dates in two adjacent rows that belong to the same object version? If so, please refer to below DAX formula:

diff =
DATEDIFF (
    CALCULATE (
        MAX ( Object[P21] ),
        FILTER (
            ALLEXCEPT ( Object, Object[OV_VERSIONPART] ),
            Object[OV_OBJECTPART]
                = EARLIER ( Object[OV_OBJECTPART] ) - 1
        )
    ),
    Object[P21],
    SECOND
)

Best regards,

Yuliana Gu