Filter Report by Max Date

[surajv3]

Hello, 

I am new to Power BI and would like help with this scenario I am not able to solve

The data that I have is as follows

ID	Title	Team	Application	ChangedDate
1	Data1	T1		8/30/2016 9:13:30 AM
1	Data1	T1	A1	8/30/2016 10:33:40 AM
1	Data1	T2	A1	9/1/2016 7:53:30 PM
2	Data2			8/30/2016 9:23:30 AM
2	Data2	T3	A3	9/1/2016 2:53:30 PM

What I would like to display in the report is only these rows that corresponds to the Max Changed Date

ID	Title	Team	Application	ChangedDate
1	Data1	T2	A1	9/1/2016 7:53:30 PM
2	Data2	T3	A3	9/1/2016 2:53:30 PM

Thanks for your help in advance,

[KGrice · Accepted solution]

Hi @surajv3. One way you can do this is by going to the Data view and adding a new column that will indicate which row contains the max changed date by ID. Here's the column I used:

IsLatestChangedDateByID = TableName[ChangedDate] = 
	CALCULATE(
		MAX(TableName[ChangedDate]), 
		FILTER(ALL(TableName), TableName[ID]=EARLIER(TableName[ID]))
	)

Using that will give you a True/False indicator that you can use in Filters, Slicers, etc.

Another way to get the same result is to use LASTNONBLANK instead of MAX:

IsLatestChangedDateByID = TableName[ChangedDate] = 
	CALCULATE(
		LASTNONBLANK(TableName[ChangedDate], 1), 
		FILTER(ALL(TableName), TableName[ID]=EARLIER(TableName[ID]))
	)

That's what I used at first, but then though the MAX solution is probably more intuitive. I only showed both so you know the options are there, and there are some cases where LASTNONBLANK will be more helpful than MAX.

[Mezmarianne]

After several tries of finding the latest date grouped by customer, this was the one that worked. Thanks.

[inescastelhano]

Hi, Thanks for your tip. However, this is not working for me. I am wondering if it is comparing row to row so it's returning 'true' for all rows. 

Thank you,

Inês Castelhano

[surajv3]

Thank you so much for your help, Used the formula with MAX 

Thanks again