Finding the difference between two dates in different rows

[Mafesa_Sasol]

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 would like to find the difference between the Start Date in second row and the End Date in the first row. Like how cumulative sum is executed. Like using Excel row and column indexes.

[Mafesa_Sasol]

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.

[Mafesa_Sasol]

Hi @Anonymous Yes it is a calculated column. I have attached your code as:

Standby Date = var endddate = Standby[End Date]

var finddate = CALCULATE(MIN(Standby[End Date]), Standby[End Date]> endddate)

var datebeg = Standby[Start Date]

return

datediff(datebeg,finddate,DAY)

In this case, the column name is Standby Date. However, I am getting the calculated column with blanks all the way.

A snapshot of the column is attached below:

The first Column is the Difference of the number of days bewteen the Start Date column. However I need the difference bewteen the Start Date and End date diagonally. For example, the number of days between the Start Date, Monday March 21, 2022 and the End Date Sunday, March 6, 2022.

[Anonymous · Accepted solution]

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

[Anonymous2024]

Hi, Can you help with excluding weekends (& holidays if possible) in the above solution you have posted?? Thank you!

[Anonymous]

Hi

I assume you make this calculation on a calculated column

var endddate=Table(End Date)

var finddate=calculate(min(Table(End Date)), Table(End Date)>enddate)

var datebeg=Table(Start Date)

return

datediff(datebeg,finddate,DAY)