Turn multiple rows with same company into one row

[senad]

Hello, 

I'm having trouble figuring this out. Any help is appreciated, thank you. 
I have a dataset, sampled below:

DealID	#	DealDate	ExitDate
910-09T	1
202-76T	1	2/22/2013
849-35T	1
362-32T	1	1/30/2015	1/30/2015
531-76T	1	8/13/2015	8/13/2015
606-25T	1	11/9/2015	11/9/2015
1709-89T	1	4/30/2020

The task is to, for each # (which represents company), take the earliest DealDate, and Latest ExitDate, and turn it into one row. Deal ID isn't needed, just there to represet each time a deal was made in database. 

So for this company, company 1, i would like for it to have only (First Deal) 2/22/2013 and (Lateset Exit)11/9/2015. 

[saidalkharusi · Accepted solution]

Hi Senad, 

1. Let's call your current table Detailed. We can start by creating a mini table called Summary that includes the distinct Company IDs:

Summary = DISTINCT(Detailed[Company ID])

2. In model view, create a relationship between Company ID in Summary table and Company ID in Detailed table.

3. In summary table, add a new column for first deal date. We'll use FIRSTDATE to get first date DealDate column in Detailed Table, and CALCULATE to filter by Company ID:

First Deal Date = CALCULATE(FIRSTDATE(Detailed[DealDate]),ALL(Detailed[Company ID]))

4.  We'll do the same for first exit date:

First Exit Date = CALCULATE(FIRSTDATE(Detailed[ExitDate]),ALL(Detailed[Company ID]))

Hope this helps.