Forum Discussion

Beaty's avatar
Beaty
New Member
8 years ago
Solved

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

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.    

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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