Subtract two dates from one table

[MauriceMecowe]

Hi,

I have one table with all the activities made on 1 appointment, as depicted on the image below. The activityId shows that the 2 records belong to the same appointment. Now I would like to have a measure or calculated column, which shows the difference in Days between the two records. I tried this in several ways, but couldn't get the desired result.

It's always the case that activityStatusValue 935000008 timestamp is subtracted by the timestamp of activityStusValue 935000000 for the same activityId.

All help is greatly appreciated!

Thanks,

Maurice

[amitchandak · Accepted solution]

@MauriceMecowe , Try a new column like

new column =
var _id =activityId
return
datediff(maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000008 ),[timestamp]),maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000000 ),[timestamp]), second)

or

new column =
var _id =activityId
return
ifr([activityStatusValue] = 935000000 , datediff(maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000008 ),[timestamp]),maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000000 ),[timestamp]), second) , blank())