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

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's avatar
    DR2022
    Helper I
    3 years ago

    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