Forum Discussion
Index column based on date, multiple entries excluded based on time
Step 1:
Index =
VAR UserFirstDate = MINX(FILTER('Events Completion';'Events Completion'[user_id] = EARLIER('Events Completion'[user_id]));'Events Completion'[DateTime])
RETURNDIVIDE (DATEDIFF(UserFirstDate ;'Events Completion'[DateTime];DAY);1)+1
Step 2:
Rank =
VAR d = 'Events Completion'[Index]
VAR c = 'Events Completion'[user_id]
RETURN
CALCULATE (
RANK.EQ ( d; 'Events Completion'[Index]; ASC );
FILTER ( ALL ( 'Events Completion'); 'Events Completion'[user_id] = c ))
Hi Phil_Seamark,
On the subsequent days the value is correct. However, if there are more days in between, those days are added to the value. The values must be an orderly one.
Like in this example (all have the same user_id):
1-06-18, 12:00 ; value 1
1-06-18, 13:00 ; value 1
2-06-18, 12:00 ; value 2
4-06-18, 12:00 ; value 3 (instead of value 4, which will be the result of the formula I posted)
Hi Phil_Seamark,
Do you have any suggestions what to do else?
Maybe doable with a RANKX?
Rank = RANKX('Events Completion';'Events Completion'[Index];;ASC;Dense)
But first it needs to be filtered by 'Events Completion'[User_ID] I guess, right?