Forum Discussion

lava_'s avatar
lava_
Icon for Helper I rankHelper I
3 years ago
Solved

How to return value based on max date within a group

I created a table of three columns that are pulling from another table. I want this created table to ultimately be grouped by ID_1, and pull in the value from ID_2 column based on the group's max Closing Date. 

 

Below is an image of the table with duplicate values just so you can see what I'm working with.

 

 

I'd like for the table to look like the below. As you can see in the image below, ID_2 is only pulling in values that match the max closing date in each group.

 

 

For this table, I'm able to get as far as grouping ID_1 by the max Closing Date using the formula below. I've also been able to create a 1-to-many relationship between this created Table (1 side) and the DealsTable (many side) that it's pulling from. How can I adjust the formula below so I can also include values from the ID_2 column?  

 

Table = 
GROUP BY(
'DealsTable',
'DealsTable' [ID_1],
"Closing Date", MAXX(CURRENTGROUP(), 'DealsTable'[ClosingDate]))

 

 

 

Thank you!

3 Replies

    • lava_'s avatar
      lava_
      Icon for Helper I rankHelper I

      This worked! Thank you so much. 

    • dthompsonKairos's avatar
      dthompsonKairos
      Icon for Helper II rankHelper II

      I used this approach, modifying the code to reflect the columns in my table, and it inexplicably just replicates another table in my report. 

      What could be happening?