cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ilcaa72
Helper IV
Helper IV

Filter values for last Date only

hello, i have a matrix and I only want to filter the lastest month of data.  So if the value exist in a different month. It should not filter the table.  The filtering should only take place if the value exist in the latest Date (in this case May)

 

what is the best approach? thannks for any assistancefilter pbi.JPG

6 REPLIES 6
Ashish_Mathur
Super User III
Super User III

Hi,

 

What exactly do you mean?  What result are you expecting?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

the last month in this Matrix is May... if I use the filter, it should only work for the column of May.  Right now it will filter the entire Matrix. i only want to Filter May (the lastest Month in the Matrix)

 

 

Hi,

 

So you only want to see those rows where in the last month of the matrix, the matched rates are between the conditions specified in the slicer.

 

Am i correct?

 

If yes, then share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

thanks.  I want to see all the data. But the filter should only take into account the values in the lastest date column (in this case May).

 

if you filter between 70-80, but May column does not contain values between 70-80 it should not filter the table, even though values 70-80 exist in another column. hope that made sense.

 

https://1drv.ms/u/s!Av69y58QJYzRjyE4igUTbHkFchba

 

thanks again.

Hi @ilcaa72,

Based on my test, you could refer to below steps:

Create two measures:

Max Month = FORMAT(MAXX(ALL('test'),IF(ISFILTERED(test[MatchedRate]),CALCULATE(MAX('test'[Date])))),"mmmm")
Filter Value = CALCULATE(SUM(test[MatchedRate]),FILTER('test','test'[Date].[Month]=[Max Month]))

Result:

1.PNG

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/hvu10z27bddn9hh/Filter%20values%20for%20last%20Date%20only.pbix?dl=0

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

thanks Daniel

 

You calculation just returns the value of the last column, (no filter on page), if I drag it onto Values, the only value that appears is the last value. 

 

I want all the values to appear in the matrix for every month.  But I only want to filter to work on the last column (most recent month). or only take into account the values of the last column.   your test setup is different from my workbook that i attached, 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - July 22, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors