Forum Discussion

DR2022's avatar
DR2022
Helper I
3 years ago
Solved

Grouping by ID and making difference between dates

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 wo...
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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's avatar
Anonymous
Not applicable
3 years ago
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's avatar
    Anonymous
    Not applicable
    3 years ago

    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