Forum Discussion
Helper IVSelect a value from minimum and maximum date by group
Dear Power BI user,
I have this kind of table where I would like to get scores based on minimum and maximum date and by group.
| Date | Group | Score |
| 01-Jan-24 | A | 678 |
| 02-Feb-24 | A | 345 |
| 03-Oct-24 | A | 129 |
| 05-Jan-24 | B | 500 |
| 18-Mar-24 | B | 400 |
| 11-Nov-24 | B | 300 |
The result should be:
| Date min | Date max | Group | Score min | Score max |
| 01-Jan-24 | 03-Oct-24 | A | 678 | 129 |
| 05-Jan-24 | 11-Nov-24 | B | 500 | 300 |
Could you please demonstrate how to create a new table?
Best regards,
Cornelis
Ah so you need the score at each of the dates? What if there are multiple rows for each group and date? I would also use different column names (like "Score at Date min" for example) to reduce the potential for misinterpretation.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zUyUdJRcgRiM3MLpVgdoLiRrltqEkLc2MQUIm6s659cghA3NLKEiJsizHECYlMDA7C4oYWub2IRQtwEJm6o65dfhhA3BonHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Group = _t, Score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Score", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Date min", each List.Min([Date]), type nullable date}, {"Date max", each List.Max([Date]), type nullable date}, {"Rows", each _, type table [Date=nullable date, Group=nullable text, Score=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Score min", each Table.SelectRows([Rows], (k)=> k[Date]=[Date min])[Score]{0},Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Score max", each Table.SelectRows([Rows], (k)=> k[Date]=[Date max])[Score]{0},Int64.Type) in #"Added Custom1"Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi CornelisV ,Here are the steps you can follow：
1. Create calculated table.
Test_Table = var _table1= ADDCOLUMNS( 'Table', "Date min", MINX(FILTER('Table',[Group]=EARLIER([Group])),[Date]), "Date max", MAXX(FILTER('Table',[Group]=EARLIER([Group])),[Date])) return SUMMARIZE( _table1,[Date min],[Date max],[Group], "Score min",SUMX(FILTER(_table1,[Date]=EARLIER([Date min])),[Score]), "Score max",SUMX(FILTER(_table1,[Date]=EARLIER([Date max])),[Score]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
6 Replies
- lbendlin
Super User
You can use the standard "Group By" transform. Switch it to Advanced mode to allow for multiple aggregations.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zUyUdJRcgRiM3MLpVgdoLiRrltqEkLc2MQUIm6s659cghA3NLKEiJsizHECYlMDA7C4oYWub2IRQtwEJm6o65dfhhA3BonHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Group = _t, Score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Score", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Date min", each List.Min([Date]), type nullable date}, {"Date max", each List.Max([Date]), type nullable date}, {"Score min", each List.Min([Score]), type nullable number}, {"Score max", each List.Max([Score]), type nullable number}}) in #"Grouped Rows"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
CornelisVDear lbendlin , thank you for your swift reply.
I'm afraid that this is not the result that I have hoped for. Not so strange because "Score min" and "Score max" correspondent to the score that belong to "Date min" and "Date max", respectively.
So, this is the desired output:
Initially, the "Group by" transform provides the solution but does not account for the score that must link with the minimum and maximum date.
Hope that this is clear and can you give me a clue how to solve this question?
Best regards,
Cornelis
- lbendlin
Ah so you need the score at each of the dates? What if there are multiple rows for each group and date? I would also use different column names (like "Score at Date min" for example) to reduce the potential for misinterpretation.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDU9UrM0zUyUdJRcgRiM3MLpVgdoLiRrltqEkLc2MQUIm6s659cghA3NLKEiJsizHECYlMDA7C4oYWub2IRQtwEJm6o65dfhhA3BonHAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Group = _t, Score = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Score", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Group"}, {{"Date min", each List.Min([Date]), type nullable date}, {"Date max", each List.Max([Date]), type nullable date}, {"Rows", each _, type table [Date=nullable date, Group=nullable text, Score=nullable number]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Score min", each Table.SelectRows([Rows], (k)=> k[Date]=[Date min])[Score]{0},Int64.Type), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Score max", each Table.SelectRows([Rows], (k)=> k[Date]=[Date max])[Score]{0},Int64.Type) in #"Added Custom1"
Thanks for the reply from lbendlin , please allow me to provide another insight:
Hi CornelisV ,Here are the steps you can follow：
1. Create calculated table.
Test_Table = var _table1= ADDCOLUMNS( 'Table', "Date min", MINX(FILTER('Table',[Group]=EARLIER([Group])),[Date]), "Date max", MAXX(FILTER('Table',[Group]=EARLIER([Group])),[Date])) return SUMMARIZE( _table1,[Date min],[Date max],[Group], "Score min",SUMX(FILTER(_table1,[Date]=EARLIER([Date min])),[Score]), "Score max",SUMX(FILTER(_table1,[Date]=EARLIER([Date max])),[Score]))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly