Return the first date based on other column

[Anonymous]

Hi,

I have a Customers table with a Customer ID, a User ID and a User Join Date. Every row contains a unique User ID. A Company ID can have multiple User ID’s assigned to it.

User ID	Company ID	User join date
1	A	1-1-2015
2	A	2-2-2015
3	B	3-3-2015
4	B	4-4-2015
5	B	5-5-2015
6	C	6-6-2015
7	D	7-7-2015
8	D	8-8-2015

I want to add a column that tells me on a Company ID level when the first user joined. The ideal result would look like this:

User ID	Company ID	User join date	Company join date
1	A	1-1-2015	1-1-2015
2	A	2-2-2015	1-1-2015
3	B	3-3-2015	3-3-2015
4	B	4-4-2015	3-3-2015
5	B	5-5-2015	3-3-2015
6	C	6-6-2015	6-6-2015
7	D	7-7-2015	7-7-2015
8	D	8-8-2015	7-7-2015

How to do that?

[AlB · Accepted solution]

Hi @Anonymous 

You can add a calculated column like this: 

Company join date =
CALCULATE (
    MIN ( Table1[User join date] ),
    ALLEXCEPT ( Table1, Table1[Company ID] )
)

[Anonymous]

Yep that's it! I really need to learn to use ALLEXCEPT more often. Thanks @AlB !