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

Filtering table by most recent data (with multiple different last entry dates)

I am having issues understanding which measures/filter I should be using to solve a problem I have with one of my Power B.I reports.

 

My report is a status report for a series of projects within a project portfolio. Each week the projects submit a weekly status update. These updates vary from week to week and cover a variety of data fields (Ive given some examples of what the data looks like in the below “DATA SET EXAMPLE”.

 

In my report I only want the most recent entry to be displayed for each project (see below “SUMMARY TABLE”). The problem I am having is using MAX DATE or a DATE FILTER doesn’t work as there are over 30 projects in the tables and they all have varying “most recent entries”.

 

DATA SET EXAMPLE

Project Update                 Project name               Project Status

1/10/2018                        Example 1                    Green 1

/10/2018                          Example 2                    Green

1/10/2018                        Example 3                    Amber

2/10/2018                        Example 1                    Amber

2/10/2018                        Example 2                    Green

2/10/2018                        Example 3                    Red

3/10/2018                        Example 1                    Green

3/10/2018                        Example 2                    Green

3/10/2018                        Example 3                    Amber

 

SUMMARY TABLE

Project name             Project Status

Example 1                 Green*

Example 2                 Green*

Example 3                 Amber*

 

*i only want the most recent row of data to be displayed in this table.

 

The date for each of these entries will vary from project to project. Any help or guidance on how to best solve this would be great appreciated.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Ross73312 Super Contributor
Super Contributor

Re: Filtering table by most recent data (with multiple different last entry dates)

I'd solve it by create a True/False DAX column like this:

displayInReport = VAR rowDate = [Project Update]
VAR projectName = [Project name]

RETURN
CALCULATE(
	MAX('YourTable'[Project Updates]) = rowDate,
	ALL('YourTable'),
	'YourTable'[Project name] = projectName
)

Then filtering on this column for TRUE.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Community Support Team
Community Support Team

Re: Filtering table by most recent data (with multiple different last entry dates)

Hi @AaronM

As tested, Ross73312's method is helpful, you need to create a caluclated column with his formula,

then add this column in a slicer, add other columns in a Table visual,

finally when you select "true" from that slicer, the table would show the lastest data for each project.

 

2.png

 

Or you could create a measure, then add this measure in the Visual Level filter, select "show items when value is 1".

Measure =
IF (
MAX ( [Project Update] )
= CALCULATE (
MAX ( [Project Update] ),
ALLEXCEPT ( 'DATA SET EXAMPLE', 'DATA SET EXAMPLE'[Project name ] )
),
1,
0
)

1.png

 

Best Regards

Maggie

View solution in original post

3 REPLIES 3
Ross73312 Super Contributor
Super Contributor

Re: Filtering table by most recent data (with multiple different last entry dates)

I'd solve it by create a True/False DAX column like this:

displayInReport = VAR rowDate = [Project Update]
VAR projectName = [Project name]

RETURN
CALCULATE(
	MAX('YourTable'[Project Updates]) = rowDate,
	ALL('YourTable'),
	'YourTable'[Project name] = projectName
)

Then filtering on this column for TRUE.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Community Support Team
Community Support Team

Re: Filtering table by most recent data (with multiple different last entry dates)

Hi @AaronM

As tested, Ross73312's method is helpful, you need to create a caluclated column with his formula,

then add this column in a slicer, add other columns in a Table visual,

finally when you select "true" from that slicer, the table would show the lastest data for each project.

 

2.png

 

Or you could create a measure, then add this measure in the Visual Level filter, select "show items when value is 1".

Measure =
IF (
MAX ( [Project Update] )
= CALCULATE (
MAX ( [Project Update] ),
ALLEXCEPT ( 'DATA SET EXAMPLE', 'DATA SET EXAMPLE'[Project name ] )
),
1,
0
)

1.png

 

Best Regards

Maggie

View solution in original post

AaronM Frequent Visitor
Frequent Visitor

Re: Filtering table by most recent data (with multiple different last entry dates)

Thanks Ross this worked perfectly! i really appreciate the help! 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 6 members 1,309 guests
Please welcome our newest community members: