How to Compare the Current Row to the Previous Row by Category Using DAX

Question

The table looks like this: [Table]:

https://i.stack.imgur.com/S48p6.png

I already create a reference column rank using RANKX to sort the table by TaskNum and its Modified_Time. Now I just need to calculate the difference between the current row to the previous row to get the time interval:

The result I expect is like this:[Expected Result]:

+ https://i.stack.imgur.com/pCyon.png

I tried to write DAX:

IF(
  Table[TaskNum] = LOOKUPVALUE(Table[TaskNum],Table[rank],Table[rank]-1),
  DATEDIFF(LOOKUPVALUE(Table[Date],Table[rank],Table[rank]-1),Table[Date],DAY)
  ,0)

I think it quite makes sense, but an error occurs: A table of multiple values was supplied where a single value was expected.

Please help me to fix it, thx!!!!!!!

Answer 1

If rank is a column in your table, you can create another dependent (on Rank) column as below for your required output-

date_diff = 

var current_date = your_table_name[Date]

var previous_date = 
CALCULATE(
    MAX(your_table_name[Date]),
    FILTER(
        your_table_name,
        your_table_name[Rank] = EARLIER(your_table_name[Rank])-1
            && your_table_name[TaskNum] = EARLIER(your_table_name[TaskNum])
    )
)

return DATEDIFF(previous_date,current_date,HOUR)/24.00

Now just convert the new column to 2 (or more as required) decimal and the output will be as below-