Forum Discussion
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 |
| 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.
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].
5 Replies
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].
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])
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?
I think you'd have to make use of the group by feature in PowerQuery. Will have a play!
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.
