Select a value from minimum and maximum date by group

[CornelisV]

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

[lbendlin · Accepted solution]

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"

[Anonymous · Accepted solution]

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

[CornelisV]

Hello @Anonymous 

That is an alternative approach in comparison to @lbendlin and both providing the same solution.

Great and a good learning point for me 😊

Best regards,

Cornelis

[lbendlin]

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.

[CornelisV]

Dear @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 · Accepted solution]

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"

[CornelisV]

Hi @lbendlin ,

Thank you for the solution. Yes, it causes a bit confusion about this question.

Your solution:

Table.SelectRows([Rows], (k)=> k[Date]=[Date min])[Score]{0}

Is something that I'm looking for.

Thank you for your support and a happy new year!

Regards,

Cornelis