Coding a Custom Column Based on Relative Earliness of Date

[mblydt-hansen]

Hi everyone,

I've been trying to code for a custom column in the advanced editor, but I've been unable to get it right. I'm hoping that someone can help me out!

My goal is to generate a "visit number" in a new column. The column would generate a numeric value for each row that is dependent upon the earliness of the visit date for each employee. For example, if employee ID "1" visits first in May 2016, then September 2016, then February 2017, the rows would have the values of 1, 2, and 3 in the new column (respectively). My dataset contains multiple distinct employee ID values, with each employee ID having multiple rows, each with unique visit dates (up to 10 separate visits). My dates are currently in the UK Format.

This is what my current dataset looks like.

This is a demo of what I want my new column to look like.

 I would be grateful if someone could assist me with coding this new custom column.

Thanks in advance!

[Anonymous · Accepted solution]

I used some of your data to scrap this up. Seems to work just fine. Understanding exactly what EARLIER() does helps here.

Visit Count = COUNTROWS(FILTER(Data, Data[ID] = EARLIER([ID]) && Data[Visit Date] <= EARLIER([Visit Date])))

[mblydt-hansen]

Thank you very much @Anonymous. I'm getting exactly what I was hoping to see. I really appreciate your help!