Max & Min Dates Only Filter (top1/bottom1)

[SiroPW]

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.

[AnthonyTilley]

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

[SiroPW]

Thanks for the reply.

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)

[v-diye-msft]

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:

[SiroPW]

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

[v-diye-msft · Accepted solution]

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. 

[SiroPW]

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