Filter virtual table to keep most recent dates by user and goal type

[wal808]

If I have users Jeff and Steve, and each can update there different goal types (products sold & cold calls).. I'd like a measure that iterates a virtual table and returns the most recent (max date) goal amount set (by name and goal type). See example below, and highlighted*** rows that result in the most recent goals to keep. Thank you in advance.

my table looks like this:

Name.     Goal.               Date set         Amt

jeff.          Prod sold.     1/1/2020.        10

jeff.          Prod sold.      2/3/2020.        15***

steve.       Cold calls.     2/15/2020.        6

Steve.      Product sold. 2/7/2020           9***

steve.       Cold calls.     3/12/202.          8***

[Anonymous · Accepted solution]

// This calc column will
// assign to each row in the raw
// table the most recent
// goal amount for the Name
// and Goal. Of course,
// the columns Amt and [Recent Goal Amount]
// should be hidden and only exposed
// to the user via measures.
[Recent Goal Amount] = // calculated column
var __name = T[Name]
var __goal = T[Goal]
return
	MAXX(
		topn(1,
			filter(
				T,
				T[Name] = __name
				&&
				T[Goal] = __goal
			),
			T[Date set],
			DESC
		),
		T[Amt]
	)
	
// Once you have the column
// it's easy to create a measure
// that will return the recent goal amount.
[RGA] =
IF(
	HASONEFILTER( T[Name] ) && HASONEFILTER( T[Goal] ),
	SELECTEDVALUE( T[Recent Goal Amount]
)

Best

D

[harshnathani]

HI @wal808 ,

Create 2 measure

Goal Date1 = 
MAXX(
SUMMARIZE('Table','Table'[Name],'Table'[Date Set],'Table'[Goal ],'Table'[Amt],"Goal Date", MAX('Table'[Date Set])),[Goal Date])

Total Amt = MAXX 
(FILTER('Table','Table'[Date Set] = [Goal Date1]),'Table'[Amt])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)