Calculate occurrence of store visit (first-time etc) based on date

[Anonymous]

Hi, I need help with the following:

I have ID's and date&time of store visit. I am looking for a way to calculate the columns on the right. If the ID skipps more than two months it should be again counted as first-time visitor. If you have any idea how to calculate this, please let me know. Thanks

ID	date & time	first-time visitor	repeater (2 months in row)	(one month skipped)	(two months skipped)
aaa	2019/11/1 14:30	1
bbb	2019/11/2 14:30	1
ccc	2019/11/2 14:30	1
ddd	2019/11/3 14:30	1
aaa	2019/12/1 14:30		1
aaa	2020/1/1 14:30		1
bbb	2020/1/2 14:30			1
aaa	2020/2/1 14:30		1
bbb	2020/2/1 14:30		1
ccc	2020/2/1 14:30				1
ddd	2020/3/1 14:30	1

[amitchandak · Accepted solution]

Create a date measure like this

last date = mixx(filter(table,table[ID] = earlier(table[ID]) && table[date] < earlier(table[date])),table[date])

if this null means first visit

datediff = datediff([last date],[date],month)

can be used to find it is continue next month or there is gap

[Anonymous]

@amitchandak 

Thanks for your reply. This measure is not working for me. 

The error message says it has too many arguments for filter function. 

[amitchandak]

 suggested both as columns. Also share calculation that is giving error in text format