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 would be in hours.

 

 

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

4 Replies

  • 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

  • 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