Get the first date for a name over multiple rows

[TimPowerBi2]

Hello Power BI Community!

I am trying to get the earliest and latest date for every name in a timeseries table. I want to do this through a calculated column so I can run further analysis on the results. It would be greatly appreciated to get some help!

Below are to examples:

Current situation

Name	Date
Michael	23-7-2023
Michael	24-7-2023
Michael	12-6-2023
Charles	5-5-2023
Charles	26-7-2023
Henk	2-2-2023
Henk	4-6-2023
Henk	15-7-2023
Henk	7-1-2023

Desired result

Name	Date	Earliest	Latest
Michael	23-7-2023	12-6-2023	24-7-2023
Michael	24-7-2023	12-6-2023	24-7-2023
Michael	12-6-2023	12-6-2023	24-7-2023
Charles	5-5-2023	5-5-2023	26-7-2023
Charles	26-7-2023	5-5-2023	26-7-2023
Henk	2-2-2023	7-1-2023	15-7-2023
Henk	4-6-2023	7-1-2023	15-7-2023
Henk	15-7-2023	7-1-2023	15-7-2023
Henk	7-1-2023	7-1-2023	15-7-2023

[Anonymous]

you can create two column 

Latest =CALCULATE(MAX(Table[Date]),ALLEXCEPT(Table, Table[Name]))

Earliest =CALCULATE(MIN(Table[Date]),ALLEXCEPT(Table, Table[Name]))

you can get the required table 

[MBernalBI · Accepted solution]

Hello!!

I hope that I can help you. I have created a calculated table. 

Desired result = 
SUMMARIZE(Tabla,  
Tabla[Name],
Tabla[Date],
"Earliest", CALCULATE(MIN(Tabla[Date]), ALLEXCEPT(Tabla,Tabla[Name])),
"Latest", CALCULATE(MAX(Tabla[Date]), ALLEXCEPT(Tabla,Tabla[Name]))
)

This is the result

I hope that I have helped you.

[TimPowerBi2]

Thank you so much for the quick solution!

It works really well thank you. The only thing that is bugging me is that despite the name field having only unique values in the new custom table I can only create a many-to-many relationship between it and the original table.

Do you know why this is? 

[MBernalBI]

Hi @TimPowerBi2 

Which fields are you using in the relationship? 

[TimPowerBi2]

Hi @MBernalBI 

I am using the Name fields.

[MBernalBI]

Hi @TimPowerBi2 

This occurs because the relationship between the Name fields is N:N. Table 1 shows N records for Michael and Table 2 also shows N records for Michael.

I would use Table 2 for future analysis, since if the relationship were established as N:N, there could be duplicate problems. It is also a bad practice in data modeling.

I hope tha it helps you and it's a good solution.

[TimPowerBi2]

Thanks a lot, that makes perfect sense and will work with it from there! 

[TimPowerBi2]

Apologies for the poor formatting upon uploading.....