getting the max and min date of a group

[wilson_smyth]

I am  having difficulty getting the min start date, and the max end date of a group of records.

The fact table contains a personID, their start and end date. There can be multiple rows for a person.

I want to get the date they started, the minDate and the day they finished, their max endDate.

Id like this to be in a calculated column.

I can do it if i use the dates in the fact table but if i use dimensions, i am stuck.
Note i cannot enable bidirectional filtering due to ambiguity issues.

Below is a screenshot of the raw data, and the end goal.
There is also a link to a pbix with the data loaded.

Thank you for any help 

[v-yulgu-msft · Accepted solution]

Hi @wilson_smyth ,

I made some modifications to your PBIX file, please if the new calculated table meets your requirement. Alternatively, you could generate MinStartDate and MaxEndDate as calculated columns in source data table.

Best regards,

Yuliana Gu

[parry2k]

@wilson_smyth try following measure

Min Date = 
CALCUATE ( MIN( Table[Start Date] ), ALLEXCEPT( Table, Table[PersonId] ) )

Max Date = 
CALCUATE ( MAX( Table[End Date] ), ALLEXCEPT( Table, Table[PersonId] ) )

[KayceVC]

@wilson_smyth If the above solution does not return the results you are looking for. Try looking at the post below.

https://community.powerbi.com/t5/Desktop/Using-Group-By-and-MAX/td-p/170699

[wilson_smyth]

@KayceVC thank you! but i have managed to figure it out if the data is all in one single table. The difficulty comes when i am using a date dimension. The value from the date dimension will be on the report, not the date from the fact table.

[parry2k]

@wilson_smyth you will have something to connect your fact table with date table. I dont see this as issue even if there is date dimension. How your fact is linked with date table? I looked at your pbix, if tht is what your model is then measures I provided would work. May be I'm missing something here or you are over thinking it.

[wilson_smyth]

@parry2k Thanks for the quick response!

 I had gotten that far, but the issue is i am using a date dimension, so i cannot use the value from the fact table.