DAX Measure to subtract dates in different rows by group

[leodm]

Database sample:

I need a DAX Measure (since I'll work with FILTERED VALUES) to calculate the difference between consecutive dates by Squad.

Thank you in advance (=

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Expected result days: =
VAR _current =
    MAX ( Data[DateTime] )
VAR _previous =
    CALCULATE (
        MAX ( Data[DateTime] ),
        OFFSET (
            -1,
            ALL ( Data[Squad], Data[DateTime] ),
            ORDERBY ( Data[DateTime], ASC ),
            KEEP,
            PARTITIONBY ( Data[Squad] )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Data[Squad] ),
        DATEDIFF ( _previous, _current, MINUTE ) / ( 24 * 60 )
    )