Forum Discussion

Mat42's avatar
Mat42
Resolver I
4 years ago
Solved

Max/Min Calculated Column

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 ...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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].

v-jingzhang's avatar
v-jingzhang
Community Support
4 years ago

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.