Get earliest and latest date based off related IDs in the same table

[MAVIE]

Hi All,

I need to create measures to get the earliest and latest dates based off of a varying number of related rows.

My data look like the following:

ID	Type	RelatedID	StartDate	EndDate
1	Primary
2	Sub	1	01-02-2022	25-01-2022
3	Sub	1	15-03-2022	20-04-2022
4	Primary
5	Sub	4	10-02-2022	17-04-2022
6	Sub	4	31-05-2022	14-06-2022
7	Sub	4	05-03-2022	06-04-2022

There is a Primary type and a Sub-type, where only the Sub-type has a defined date range.

I then need to create a date range for the Primary type, based off the earliest StartDate and latest EndDate from the Sub-type with RelatedID. It is also possible for a Primary type to have no Sub-type, therefore it is possible that no date range can be created for some Primary types.

I have tried various methods but have run out of idea for how to solve this. 
It is also possible that two calculated columns would be a better solution. 

Any help would be greatly appreciated

[_MG_ · Accepted solution]

Hey Mavie,

You could do that in a way using the implicit row context in calculated columns.
Result:

StartDatePrimary =

VAR CurrentID = Table1[ID]

VAR SubTable = Filter(Table1, AND(Table1[Type] = "Sub", Table1[RelatedID] = CurrentID))

return

MINX(SubTable, Table1[StartDate])

EndDatePrimary =

VAR CurrentID = Table1[ID]

VAR SubTable = Filter(Table1, AND(Table1[Type] = "Sub", Table1[RelatedID] = CurrentID))

return

MAXX(SubTable, Table1[EndDate])

StartDateFinal = IF(ISBLANK(Table1[StartDate]), Table1[StartDatePrimary], Table1[StartDate])

EndDateFinal = IF(ISBLANK(Table1[EndDate]), Table1[EndDatePrimary], Table1[EndDate])

I hope this will help you 🙂 

_MG_

[MAVIE]

Thank you, this seems to work as I intended 😊