CODEWITHSUNDEEP
powerbidaxpowerquery
lifeofthenoobie
lifeofthenoobie

Reputation: 169

Calculate table with max date and max value from other table

I would like to create a table basic on two criteria - max date for ID, and if date is the same, then max value for the date. If it happens that date and value is the same for all lines for certain ID - just take one line for that ID

Can be done in either DAX or PQ (if PQ is it even possible to not have many steps?)

My initial table

id date value
ID1 01.01.2022 100
ID1 01.01.2022 150
ID1 01.02.2022 100
ID2 01.03.2022 150
ID2 01.03.2022 100
ID3 01.04.2022 300
ID3 01.04.2022 300
ID3 01.04.2022 300

Expected output

id date value
ID1 01.02.2022 100
ID2 01.03.2022 150
ID3 01.04.2022 300

Thank you in advance for help

Upvotes: 0

Views: 5083

Answers (3)

horseyride
horseyride

Reputation: 21393

Your question does not match the sample data. If you find the max date for an ID, then how can the same ID appear twice in the output like you show for ID1? The max value for ID1 on 01.02.2022 is 150, not 100, so how does that match the request for max value for the max date?

That said, if you are really asking "Find the max date, and max value on that date" by ID, then you can use below in PowerQuery, which groups and sorts, then takes the first row to return

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"date", type date}, {"value", Int64.Type}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {{"Count", each Table.Sort(_,{{"date", Order.Descending}, {"value", Order.Descending}}){0} }}),
#"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"date", "value"}, {"date", "value"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count",{{"date", type date}})
in #"Changed Type1"

enter image description here

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60379

Assuming your output table is incorrect, and should really be:

enter image description here

Then you can

  • Group by ID
  • Return the row with the Max value in each subtable

M Code

let

//change next line to reflect actual data sourdce
    Source = Excel.CurrentWorkbook(){[Name="Table23"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"id", type text}, {"date", type date}, {"value", Int64.Type}}),

//Group by ID
//Aggregate by max value
    #"Grouped Rows" = Table.Group(#"Changed Type", {"id"}, {
        {"Count", (t)=> Table.Max(t,"value")}}),

//expand each subtable and set the data types
    #"Expanded Count" = Table.ExpandRecordColumn(#"Grouped Rows", "Count", {"date", "value"}, {"date", "value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Count", {{"date", type date},{"value", Int64.Type}})
in
    #"Changed Type1"

Upvotes: 1

Jody Highroller
Jody Highroller

Reputation: 1029

Using PowerQuery:

  • Group By ID and Date to get your MaxValue:
    • enter image description here
  • You should now have these columns:
    • enter image description here
  • Now Group By just the ID column. Add aggregations for MaxDate and All Rows
    • enter image description here
  • Expand the All column to show the date and MaxValue columns
    • enter image description here
  • Filter the date column for all dates that equal MaxDate. The easiest way to do this is using the GUI to filter the date column to equal today, and then replace the code that is output.
    • Generated Code: = Table.SelectRows(#"Expanded All", each [date] = #datetime(2022, 8, 3, 0, 0, 0))
    • Modified Code: = Table.SelectRows(#"Expanded All", each [date] = [MaxDate])

Upvotes: 1

Related Questions