First Sales Date with Sub Accounts

[Anonymous]

Hello!

I'm tracking the 2018 sales for customers whose first date of sale was in 2016 or 2017. Basically I'm looking at new customers from 2016/17 and how they are doing this year. That seems easy enough, but there is one minor wrinkle that is causing me headaches.

Our customers each have a unique customer number "CustNo", but there are cases where a customer might have multiple accounts. When a customer is verified as having multiple accounts, all of their accounts are labelled Sub Account and is denoted on our Customer Table in the column "Is SubAccount". To mark sub accounts that are related to each other we also have a "SubAccount" column on the Customer Table. Here, all related subaccounts are given an umbrella name they fit under. An account without any sub accounts attached simply display their customer number in the "SubAccount" column.

For example:

Customer numbers AAA123, AAB124, and ABB321 are all sub accounts, in the "SubAccount" column they are listed "GROUP1". Customer number CCC789 is not tied to any sub accoounts, the "SubAccount" column will display CCC789.

Table1:

CustNo	Is SubAccount	SubAccount
AAA123	Yes	GROUP1
AAB124	Yes	GROUP1
ABB321	Yes	GROUP1
CCC789	No	CCC789

Problem:

If a customer is part of a sub account group, I must look at the earliest first sale date out of that group. If that date falls before 2016-2017 they will not be looked at, even if one of the sub accounts falls within that timeframe. If the earliest first sale date within the sub account group DOES fall within that time frame, I need to calculate the entire groups sales. 

Table2:

CustNo	Is SubAccount	SubAccount	First Sale Date
AAA123	Yes	GROUP1	3/2/2016
AAB124	Yes	GROUP1	5/14/2017
ABB321	Yes	GROUP1	2/10/2018
CCC789	No	CCC789	10/3/2017
ZZZ123	Yes	GROUP2	7/18/2013
ZZZ321	Yes	GROUP2	12/1/2016
ZZZ789	Yes	GROUP2	9/22/2017

**In Table2, all Customer Numbers in "GROUP1" along with CC789 will be counted. Whereas no customers in "GROUP2" will be counted due to ZZZ123's first sale in 7/8/2013.

 What I've Tried  

I have attempted making calculated columns utilizing SUMMARIZE( ) to group customers by the SubAccount column and then look for FIRSTDATE( ) or MINA( ) in the First Sale Date column. All I've been able to get is the earliest first sale date among all customers regardless of sub account which isn't very helpful.

Is there any way to create a calculated column which will look at my SubAccount column and return the earliest first sale date among that specific group? This calculated column "Earliest Date" I'm imagining will return values like this

Table3:

CustNo	Is SubAccount	SubAccount	First Sale Date	Earliest Date
AAA123	Yes	GROUP1	3/2/2016	3/2/2016
AAB124	Yes	GROUP1	5/14/2017	3/2/2016
ABB321	Yes	GROUP1	2/10/2018	3/2/2016
CCC789	No	CCC789	10/3/2017	10/3/2017
ZZZ123	Yes	GROUP2	7/18/2013	7/18/2013
ZZZ321	Yes	GROUP2	12/1/2016	7/18/2013
ZZZ789	Yes	GROUP2	9/22/2017	7/18/2013

I could then use this new calculated column to filter out customers I don't want to look at.

Any ideas?

Thanks for reading and in advance for any help you can give.

[v-xjiin-msft · Accepted solution]

Hi @Anonymous,

In your scenario, you want to get the earliest date based on each SubAccount group. Right?

To achieve this, you can try to use ALLEXCEPT() function to make groups. Please refer to following calculated column:

Earliest Date =
CALCULATE (
    MIN ( 'First Sales'[First Sale Date] ),
    ALLEXCEPT ( 'First Sales', 'First Sales'[SubAccount] )
)

Thanks,
Xi Jin.

[Anonymous]

Hello @v-xjiin-msft,

Thank you so much! This calculation is so much simpler than what I had tried. This calculation achieves exactly what I wanted.

If you don't mind, could you explain the detail in how this works? I had tried ALLEXCEPT() before and it wasn't working out, is this just preventing the 'First Sales' table from breaking up the 'SubAccount' groups before the MIN() function completes?

Thanks again,