cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
surajv3 Frequent Visitor
Frequent Visitor

Filter Report by Max Date

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

 

IDTitleTeamApplicationChangedDate
1Data1T1 8/30/2016 9:13:30 AM
1Data1T1A18/30/2016 10:33:40 AM
1Data1T2A19/1/2016 7:53:30 PM
2Data2  8/30/2016 9:23:30 AM
2Data2T3A39/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

 

IDTitleTeamApplicationChangedDate
1Data1T2A19/1/2016 7:53:30 PM
2Data2T3A39/1/2016 2:53:30 PM

 

Thanks for your help in advance,

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
KGrice Established Member
Established Member

Re: Filter Report by Max Date

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.

 

LASTNONBLANK.PNG

 

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.

4 REPLIES 4
Highlighted
KGrice Established Member
Established Member

Re: Filter Report by Max Date

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.

 

LASTNONBLANK.PNG

 

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.

surajv3 Frequent Visitor
Frequent Visitor

Re: Filter Report by Max Date

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

 

Thanks again

 

inescastelhano Frequent Visitor
Frequent Visitor

Re: Filter Report by Max Date

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

Mezmarianne Regular Visitor
Regular Visitor

Re: Filter Report by Max Date

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