Forum Discussion
Get the first date for a name over multiple rows
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 |
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.
8 Replies
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.
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?
Apologies for the poor formatting upon uploading.....
