Grouping by ID and making difference between dates

[DR2022]

Hi! I need help with a function. How can I get the difference between the previous FinishDate and the StartDate and after group by ID from the data table below? It can be in M ​​or DAX. The result would be in hours.

 

[Anonymous]

compareDate =

VAR startdate = Standby[Start Date]

VAR maxfinishdate =

    CALCULATE (

        MAX ( Standby[FinishDate] ),

        FILTER ( ALL ( Standby ), Standby[finishDate] < startdate,allexcept(Stanby,Stanby[ID])

    )

VAR result =

    DATEDIFF ( startdate, maxfinishdate , DAY )

RETURN

    result

[Anonymous · Accepted solution]

Sorry allexcept does not work on this dax code

sue this instead

compareDate =

VAR startdate = Standby[Start Date]

var searchid = Standby[ID]

VAR maxfinishdate =

    CALCULATE (

        MAX ( Standby[End Date] ),

        FILTER ( ALL ( Standby ), Standby[End Date] < startdate&&Standby[ID]=searchid)

    )

VAR result =

    DATEDIFF ( startdate, maxfinishdate , DAY )

RETURN

    result

[Anonymous]

Hi Please find the code :

compareDate =

VAR startdate = Standby[Start Date]

VAR maxfinishdate =

    CALCULATE (

        MAX ( Standby[FinishDate] ),

        FILTER ( ALL ( Standby ), Standby[finishDate] < startdate )

    )

VAR result =

    DATEDIFF ( startdate, maxfinishdate , DAY )

RETURN

    result

[DR2022]

It worked for the dates! But I think you have to create a variable to group by ID before making the difference between dates. Grouping dates by a, b, c, d. That's what I'm not able to do. And the result of your function is being a negative number