Max/Min Calculated Column

[Mat42]

This is either going to be easy or horribly complicated.

Let's say I ave a simple table of data:

Name	Date
Dave	01/01/2021
Dave	01/01/2021
Dave	02/02/2019
Dave	01/01/2021
Steve	12/10/2020
Steve	13/10/2020
Steve	14/10/2020
Steve	15/10/2020

All I want to be able to do is add in 2 columns that will show me the earliest date and latest date for each person, so it would look like this:

Name	Date	MinDate	MaxDate
Dave	01/01/2021	02/02/2019	01/01/2021
Dave	01/01/2021	02/02/2019	01/01/2021
Dave	02/02/2019	02/02/2019	01/01/2021
Dave	01/01/2021	02/02/2019	01/01/2021
Steve	12/10/2020	12/10/2020	15/10/2020
Steve	13/10/2020	12/10/2020	15/10/2020
Steve	14/10/2020	12/10/2020	15/10/2020
Steve	15/10/2020	12/10/2020	15/10/2020

Am I missing something obvious? Because I can't work this out.

[v-jingzhang]

Hi @Mat42 

Use the Group by feature under Transform tab and set up three aggregation columns like in the following image. 

After grouping, expand "All" column and select only Date column to expand. 

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckksS1XSUTIw1AciIwMjQ6VYHUKiRvpAZGRgaIlHbXBJKljY0Ejf0AAkbIAqbIxd2AS7sClCOBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source, {{"Name", type text}, {"Date", type date}}, "en-GB"),
  #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"All", each _, type table[Name = nullable text, Date = nullable text]}, {"MinDate", each List.Min([Date]), type nullable text}, {"MaxDate", each List.Max([Date]), type nullable text}}),
  #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Date"}, {"Date"})
in
  #"Expanded All"

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Mat42]

Thanks for the replies, guys, that's really helpful.

I should have said that it needs to be created in Power Query as this is for a dataflow. Can this be replicated in M?

[bcdobbs]

I think you'd have to make use of the group by feature in PowerQuery. Will have a play!

[bcdobbs · Accepted solution]

Your calculated column needs to look like:

Min Date = 
    CALCULATE (
        MIN ( Person[Date] ),
        ALLEXCEPT( Person, Person[Name] )
    )

If you just did MIN on it's own it would find the minimum date in the whole column.

CALCULATE forces a context transition that moves all the columns in the current row into the filter context.

However CALCULATE ( MIN ( Person[Date] ) ) will just return the date of the current row because CALCULATE has included the date column in the filter.

The solution above tells calculate to remove the filters from everything apart from Person[Name].

[amitchandak]

@Mat42 , Based on what I got

new columns

Min date = minx(filter(Table, [Name] =earlier([Name]) ), [Date])

Max date = maxx(filter(Table, [Name] =earlier([Name]) ), [Date])