Forum Discussion

Mafesa_Sasol's avatar
Mafesa_Sasol
Frequent Visitor
3 years ago
Solved

Finding the difference between two dates in different rows

Hi, I would really appreciate any assistance. I am trying to find the difference between the Start and End Date but in different rows and different columns. For example, in the attached data table, I...
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi mafesa sasol,

     

    Please find the code :

    Standby Date =
    VAR enddate = Standby[End Date]
    VAR startdate = Standby[Start Date]
    VAR minstartdate =
        CALCULATE (
            MIN ( Standby[Start Date] ),
            FILTER ( ALL ( Standby ), Standby[Start Date] > startdate )
        )
    VAR result =
        DATEDIFF ( enddate, minstartdate, DAY )
    RETURN
        result

     

     
Mafesa_Sasol's avatar
Mafesa_Sasol
Frequent Visitor
3 years ago

Hi Anonymous 

Thank you very much. It works perfectly. However, in some cases, I get incorrect results. For example, in the attached snapshop. The results are in correct order for each user, however, the last row represents a new user where the difference between the Start and End Date is from June to April and gives me 8 which is weird. How do I filter these such that when the execution is a new user row, then a blank value is resulted or something at the beginning of a new user row. 

 

How can I navigate around that. Actually, I want to filter the results by the unique id values. Is this error because of the extra additional columns in my table or what? also, I used the Filter ALLEXCEPT instead of ALL because I was getting incorrect negative values.