Forum Discussion
Finding Consecutive Records - Weekly Buckets
Hi All,
Trying to find a way to identify users that have logged in for x consecutive weeks. Sample data below:
| Date | UserID |
| 22-01-20 | ABC |
| 29-01-20 | ABC |
| 05-02-20 | ABC |
| 12-02-20 | ABC |
| 12-02-20 | XYZ |
| 19-02-20 | ABC |
| 19-02-20 | XYZ |
| 26-02-20 | ABC |
| 26-02-20 | XYZ |
So if we are looking at last 4 weeks consectitive use, user ABC would be identified as TRUE as they have logged in at least once over the last 4 weeks. Whereas user XYZ has logged in 3 of the 4 last weeks and would come up as FALSE.
I had a go at the formula based on other threads and some research:
=CALCULATE(
SUM( Data[UserID] ),
DATESINPERIOD(
Data[Date],
LASTDATE( Data[Date]),
-(7*4-1),
DAY
)
)
It doesn't quite provide me with what I am after after I check it. Any ideas?
3 Replies
DataMark , Prefer to create a date table and join this date with date of date table
add these in Week/Date table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)and try measure like
Last 4 weeks = CALCULATE(distinctcount('Date'[Week]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])), not(isblank(Table[UserID])))
Final measure = if([Last 4 weeks]<4, blank(), [Last 4 weeks])
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Thanks for the reply.
What does the Week / Date table look like? Do I simply have the dates and then the WEEKNUM()? If this is the case, will this work if the data spans over 52 weeks, so multiple identical WEEKNUM() values?
