cancel
Showing results for
Did you mean:
Frequent Visitor

## Max & Min Dates Only Filter (top1/bottom1)

HI,

I have a dataset like follows:

 Date Value 2019/08/01 10 2019/08/01 20 2019/08/01 30 2019/08/01 40 2019/08/01 50 2019/07/01 100 2019/07/01 200 2019/07/01 300 2019/06/01 1 2019/06/01 2 2019/06/01 3 2019/06/01 4 2019/05/01 1000 2019/05/01 2000

What I would like to do is be able to filter on the max and minimum dates so the result would be:

 Date Value 2019/08/01 10 2019/08/01 20 2019/08/01 30 2019/08/01 40 2019/08/01 50 2019/05/01 1000 2019/05/01 2000

I could achieve the same effect by manually ticking the 'Date' basic filter but I would have to change it every time the dataset updates. Similarly I could do topN filters for top and bottom dates but can't apply both at the same time AFAIK.

Any help is appeciated, thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support

## Re: Max & Min Dates Only Filter (top1/bottom1)

Hi @SiroPW ,

The formula in your measure is missing another condition:

`Return IF(MAX('Table'[Date])=maxd||MAX('Table'[Date])=mind,"Yes","No")`

I attached my pbix here for your reference, also please share yours if you are still stuck in it.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
6 REPLIES 6
Super User I

## Re: Max & Min Dates Only Filter (top1/bottom1)

i would use a calculated colunm to check if the date is equal to the max or min date and then filter on this colunm

example colunm below

MAX&MIN =
-- first get the max date as a veriable
var Maxd = max('Table'[Date])
-- next get the min date as a veriable
var mind = min('Table'[Date])
-- get the date in the row
var d= 'Table'[Date]
-- check if the date is equal to eaither of the two dates and if it is return TRUE if not RETURN FALSE
return if(or(d = Maxd,d =mind),TRUE, FALSE)

this colunm can then be used as a filter in the table to display only those that are true

Proud to be a Super User!

Frequent Visitor

## Re: Max & Min Dates Only Filter (top1/bottom1)

Do you know how I could modify this to work with only the filtered/selected values?

I tried ALLSELECTED like below but I'm getting an error "The MAX function only accepts a column reference as an argument".

MAX&MIN =

-- first get the max date as a veriable

var Maxd = max(ALLSELECTED('Table'[Date]))
-- next get the min date as a veriable
var mind = min(ALLSELECTED('Table'[Date]))
-- get the date in the row
var d= 'Table'[Date]
-- check if the date is equal to eaither of the two dates and if it is return TRUE if not RETURN FALSE
return if(or(d = Maxd,d =mind),TRUE, FALSE)
Community Support

## Re: Max & Min Dates Only Filter (top1/bottom1)

Hi @SiroPW ,

You can take try of this one:

```MAX&MIN = var maxd = MAXX(ALLSELECTED('Table'),[Date])
var mind = MINX(ALLSELECTED('Table'),[Date])
Return IF(MAX('Table'[Date])=maxd||MAX('Table'[Date])=mind,1,0)```

When the date were filtered, it still works:

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

## Re: Max & Min Dates Only Filter (top1/bottom1)

Thanks, I've tried a lot of combinations/tweaks of your answer but I still can't quite get it to filter properly.

It basically ends up returning True/1 for every record.

My latest attempt was to strip it back:

MAX&MIN =
var maxd = MAXX(ALLSELECTED(portfolio_eom),[as_at_date])
var mind = MINX(ALLSELECTED(portfolio_eom),[as_at_date])
Return IF(MAX(portfolio_eom[as_at_date])=maxd,"Yes","No")

I can see the maxd is returning the correct date and MAX(portfolio_valuation_eom[as_at_date]) by itself is too (they match), however every record returns "Yes".

Any ideas why? I feel like it should be something simple I'm missing. Thanks
Frequent Visitor

## Re: Max & Min Dates Only Filter (top1/bottom1)

I think the problem is the ALLSELECTED is not working my my Date slicer.

Community Support

## Re: Max & Min Dates Only Filter (top1/bottom1)

Hi @SiroPW ,

The formula in your measure is missing another condition:

`Return IF(MAX('Table'[Date])=maxd||MAX('Table'[Date])=mind,"Yes","No")`

I attached my pbix here for your reference, also please share yours if you are still stuck in it.

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

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors