cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mblydt-hansen Frequent Visitor
Frequent Visitor

Coding a Custom Column Based on Relative Earliness of Date

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.

 

2018-06-04.pngThis is what my current dataset looks like.

 

2018-06-04 (1).pngThis 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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Anonymous
Not applicable

Re: Coding a Custom Column Based on Relative Earliness of Date

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])))

 

 

image.png

2 REPLIES 2
Anonymous
Not applicable

Re: Coding a Custom Column Based on Relative Earliness of Date

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])))

 

 

image.png

mblydt-hansen Frequent Visitor
Frequent Visitor

Re: Coding a Custom Column Based on Relative Earliness of Date

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 390 members 3,676 guests
Please welcome our newest community members: