Difference in dates last day of session and first to first day of session

[PowerBI__]

Hello,
I have a table which has name and date in table. In this table people visits date and time are mentioned. I want do date difference based on a criteria. People visits website continously in a day or two. My data looks like below:

James	9/7/2023 22:28
James	9/8/2023 8:33
James	9/9/2023 13:23
James	9/10/2023 17:16
James	9/11/2023 22:31
James	9/29/2023 19:54
James	9/30/2023 9:26
James	9/31/2023  9:26
James	10/15/2023 23:41
James	10/16/2023 18:21

I want if they person do not visit till 3 days lets say in next visits after few days he visited after 18 days so if he visits after more than 3 days then I want the difference of 29 september and 7 september . It should be a column for dax. Now when last row has come again he wil visit continously and will not visit again for few days again then same criteria will work where the next time when he will come we will subtract the 29 september date from it to see again after how many days he came back again.
I also want another measure where I want to see how many days a person spent in total while he was active which would be last active day which is 11 september and 1st working day which is 7 september. Your help would be highly appreciated.

Difference of date for first criteria will look like below:

9/29/2023 19:54	9/7/2023 22:28
10/15/2023 23:41	9/29/2023 19:54

For second criteria it will look like below:

9/11/2023 22:31	9/7/2023 22:28
9/31/2023  9:26	9/29/2023 19:54

Thank you

[Anonymous · Accepted solution]

Hi, @PowerBI__ 

Based on your information, I create a table:

You can try creating a new column to calculate the date difference, and here is DAX

DateDiff = 
VAR CurrentDate = 'Table'[Date]
VAR PreviousDate = CALCULATE(MAX('Table'[Date]), 
                             FILTER('Table', 
                                    'Table'[Name] = EARLIER('Table'[Name]) && 
                                    'Table'[Date] < EARLIER('Table'[Date])
                                    )
                            )
RETURN
DATEDIFF(PreviousDate, CurrentDate, DAY)

Create a new column to calculate the active days, and here is DAX

ActiveDays = 
VAR DateDiff = 'Table'[DateDiff]
RETURN
IF(DateDiff <= 3, DateDiff, BLANK())

You can then work with the slicer to calculate the desired date

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.