Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bhmiller89
Helper V
Helper V

Showing First Entry in a Table

I have a table called "Opps Production" where all sales data is listed. One column, "Organization" Does not have unique values because our clients (Organizations) have multiple sales. Each sale is also associated with a job number. So Client "X" could have multiple sales over the course of years/months

 

Organization | Job Number | Sale Value $

X| 2017-DLL-J01| $100,000

X|2016-DLL-J09 | $40,000

 

etc etc. 


I'm trying to write a measure or a column to show new clients. That is, any Sale that is the first for a particular "Organization." 

 

I need to be able to create a filter that says "show me any new client where the sale was made in the last 90 days" 

3 REPLIES 3
Greg_Deckler
Super User
Super User

Could you grab the MIN and MAX of the date by client and compare them, if they are equal, then that is a new customer? You would probably have to create a custom column where you parse your Job Number out into a date field, it might get a little ugly given that format below, buy potentially doable.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler unfortunately no, that won't work. When a new client has a sale made, sometimes multiple jobs are executed at once. They can purchase a recurring service agreement in addition to a one time project. So a new client could potentially have multiple jobs within the span of a few days but still be considered "new."

Sounds like you would instead need to define the span of time that a customer could be "new" and then take the MIN of the date and do a DATEDIFF with TODAY() and see if it is greater than a certain number. Something like:

 

DATEDIFF(MIN([Date]),TODAY()) > 5

Would still need that date.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.