Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
surajv3
New Member

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
KGrice
Memorable Member
Memorable Member

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.

View solution in original post

4 REPLIES 4
KGrice
Memorable Member
Memorable Member

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.

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

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

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

 

Thanks again

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.