Forum Discussion

DataMark's avatar
DataMark
Regular Visitor
5 years ago

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 ...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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.