Making DAX code more efficient - counting unique Start dates in overlapping date ranges

Question

I have a table of every product purchased by every client over 25 years. The table contains client#, product, start date, and end date. The products can be owned by the client for any amount of time (1 day to 100 years). While the client owns products with us, the client is active. If a client ends all products they cease to be a client. I want to count new client starts each year. The problem is, some clients end all products then start purchasing products again years later (but clients always retain the same client#) - If the client leaves then rejoins year's later I want to count the client as a new client.

I have created DAX code to do this which works perfectly on a small file, but the code uses up too many resources and so I cannot use it on my data (about 200,000 records). I know my code is HIGHLY INEFFICIENT and could probably be cleaned up...but I am not sure how. Alternately, if I could figure out how to make these columns in PowerQuery, perhaps that would work

Here is how I do it.

1) Add four calculated columns to my table:

VeryFirstStart = Calculate(
  Min('Products'[StartDate]), 
  ALLEXCEPT(Products,Products[ClientNumber]))=Products[StartDate]

this flags records that contain the first ever start date of any client

MaxEndDateofEarlierDates = Calculate(
  Max('Products'[EndDate]), 
  Filter( 
    Filter(ALLEXCEPT(Products, Products[ClientNumber]), Products[EndDate]), 
    Products[StartDate] < EARLIER(Products[StartDate])))

This step blows up my PowerBI - this shows the date of any NEW product purchases where the new start date occurs AFTER an ending date

Second+Start = And(
  Products[MaxEndDateofEarlierDates]<>BLANK(), 
  Products[MaxEndDateofEarlierDates]<Products[StartDate])

this flags records where we want to count the new start date as a new client

NewStart = OR(Products[Second+Start],Products[VeryFirstStart])

**this flags ANY new client start date regardless of whether it was the first or a subsequent*

Finally I added this measure:

!MemberNewStarts = CALCULATE(
  DISTINCTCOUNT(Products[ClientNumber]), 
  FILTER(
    'Products', 
    ('Products'[StartDate] <= LASTDATE('DIMDate'[Date]) && 
    'Products'[StartDate]>= FIRSTDATE('DIMDate'[Date]) && 
    Products[NewStart]=TRUE())))

Does anyone have any suggestions about how to achieve this with less resources?

Thanks

Here is some data to try

MemberNumber    Product StartDate   EndDate Note (not in real data) 
1   A   02/02/2003  02/02/2004  
1   C   02/02/2009  02/02/2010  
2   A   02/02/2001  02/02/2002  
2   C   02/02/2001  02/02/2002  
2   B   02/02/2005  02/02/2010  
3   C   02/02/2002  02/02/2005  
3   B   02/02/2002  02/02/2005  
3   A   02/02/2003  02/02/2008  
4   B   02/02/2002  02/02/2003  
4   C   02/02/2003  02/02/2006  
5   B   02/02/2003  02/02/2007  
5   C   02/02/2005  02/02/2010  
5   A   02/02/2005  02/02/2007  
6   A   02/02/2001  02/02/2006  
6   C   02/02/2003  02/02/2007  
7   B   02/02/2001  02/02/2004  
7   A   02/02/2001  02/02/2005  
7   C   02/02/2005  02/02/2006  
8   B   02/02/2002  02/02/2006  
8   A   02/02/2004  02/02/2009

note member 1 starts as a new client in 2009 since all previous products ended in 2004 and member 2 starts as a new client in 2005 since all previous products ended in 2002

The desired outcome is:

Start Year  2001    2002    2003    2004    2005    2006    2007    2008
New Clients 3   3   2   0   1   0   0   0

Gordon K Gordon K · Accepted Answer · 2018-01-19T13:10:08

Here's one way of trying to solve it. Let me know if this is any more efficient than yours:

1st New Column:

PreviousHighestFinish:=  
    Calculate(
              Max(Products[EndDate]),
              ALLEXCEPT(Products,Products[ClientNumber]),
              Products[StartDate] < Earlier(Products[StartDate]
             )

This will give you the latest end date where the Client Number matches and the start date is before the current start date. If there is no earlier start date, it returns a blank.

2nd New Column:

NewClientProduct:=
      if(Products[StartDate]>=Products[PreviousHighestFinish],1,0)

This will give you a 1 for every row where the client has either not been seen before (and the previous column showed blank) or the client has ben seen before, but has no current products.

The problem with this measure is that if you have a client starting more than one product on the same date, they will show as multiple new clients.

The fix for this is to count up the instances of each client-date combination

3rd New Column:

ClientDateCount:=
    CALCULATE(
        COUNTROWS(Products),
        ALLEXCEPT(Products,Products[ClientNumber],Products[StartDate])
             )

This essentially gives the number of times that the client on this row in the table has started a product on this date.

Now divide the 2nd new column by this one

4th New Column:

NewClients:=
    DIVIDE(Products[NewClientProduct],Products[ClientDateCount])

And voila:

Making DAX code more efficient - counting unique Start dates in overlapping date ranges

1 Answers