Calculate number of days between 2 dates in different rows

[Anonymous]

Hello community members, 

I have the following question: 

A client can have a type of therapy(Product) more than once. Now I need to know the time (in days) between the Enddate of the 1st therapy and the Startdate of the next therapy from the same sort. (e.g. the time between Wmo1A 1-5-2021, Enddate first therapy, and 1-6-2021, startdate second therapy). Can someone help me with the correct formula for the calculated column? 

Hope to hear soon from you! 

Best regards, Sander

Client	IndicationNumber	Product	Startdate	Enddate
Sander1984	df1sda651f	Wmo1A	1-1-2021	1-5-2021
Sander1984	84f6as4f65	Jeugd2C	1-1-2021	1-12-2021
Sander1984	564g1f32sdg	Wmo1A	1-6-2021	1-11-2021
Sander1984	fadas5f465d	Jeugd2C	1-2-2022
Sander1984	f465dsaf1d3	Wmo1A	1-4-2022
Piet1910	98df7a98d	Wmo1A	1-1-2021	1-10-2021
Piet1910	34kl34jkld0	Wmo1A	13-4-2022

[Fowmy · Accepted solution]

@Anonymous 

Please add the following column

Days = 

var __client = [Client]
var __prod = [Product]
var __startdate = [Startdate]
var __t = 
    FILTER(
        Table11,
        Table11[Client] = __client && Table11[Product] = __prod
    )
var __lastdate = maxx( FILTER( __t,Table11[Enddate] < __startdate ) , Table11[Enddate])
return
IF(
    __lastdate <> BLANK(), INT([Startdate] - __lastdate)
)

[Anonymous]

@Fowmy, thank you! This works perfect! 

[amitchandak]

@Anonymous , Try a new column like

new column =
var _client = [client]
var _end = [Enddate]

return
datediff([Enddate] ,minx(filter(table, [client] =_client && [Start Date] > _end), [Start Date]) , day)



or

new column =
var _client = [client]
var _st = [Start Date]
return
datediff([Enddate] ,maxx(filter(table, [client] =_client && [Enddate] < _st), [Enddate]) , day)

[Anonymous]

@amitchandak Almost perfect, I only miss the combination with the product...