finding nth (5th) placed value in last 3 months

David_S · Jun 24, 2015

Hi. I’m trying to find the dax for the nth (say 5^th) best placed item achieved by an individual in a 3 month period.

I have a 3 column data table with a surname, the period (Eomonth date) and a list of unique times. I also have a related calendar table and the month flag on this table is used as a slicer on the report. IF April 15 is selected from the slicer, the period being reviewed should be Feb 15, Mar 15 and Apr 15.

I have tried the formula:

=minx(topn(5,data table,[min l3m]),[min l3m])

Where [min l3m] = Calculate(min('Table'[time]),
all(Calendar),
Filter(all(Calendar),(Calendar[Monthid]<=min(Calendar[Monthid])
&&Calendar[Monthid]>=min(Calendar[Monthid])-2)))

This formula will return the 5th best item for each surname but for the select month (ie April in my example). I can not get it to return the 5^th best item across all 3 months.

Many thanks for any help.

David

scottsen · Jun 24, 2015

=minx(topn(5,data table,[min l3m]),[min l3m])

This one is hard to say without digging in and playing with it. I would kinda want to play w/ the bold part, maybe wrap it in an ALL?

David_S · Jun 24, 2015

HI Scott. Thanks for coming back to me. The file i have is purely fictious and small ... I'm happy to share it if there is a way to do this? (the data table is only 50 rows and the calendar 6 - much bigger in the real model - and / or can be easily recreated). Adding an "all" brought back the 5th highest value across the whole date set for every month....thanks David

scottsen · Jun 24, 2015

Sure, you can send a GoogleDrive/DropBox/OneDrive link and I'll take a look.

David_S · Jun 24, 2015

https://www.dropbox.com/s/qlsjidbsd77vaqy/Quartile points v2.xlsx?dl=0

Hi Scott. Hopefully here is a link to the file. Many thanks (again) for looking at this. Regards David

David_S · Jun 24, 2015

https://www.dropbox.com/l/JheaqPK752syXMtdKna1jq

sorry this might be the link

David_S · Jun 25, 2015

Hi.
I’ve substituted the “data table” code in the existing topn (5, data table…. with

filter(
allexcept('table','Table'[Name]),
'table'[Month]<=max(calendar[Month])
&&'table'[Month]>=max(Calendar[Month])-75
)

and this appears to work at least in my test model (there may be performance issues on a larger table? )
The 75 = 75 days to ensure the dax looks back 2 months from the current month to give the rolling 3 month perspective (dates are currently stated as end of month dates only).
The Allexcept(‘table’,’table’[name]) ensures the 5^th rank item for each individual comes back and not just the 5^th overall.
I confess to not fully understand what the “filter” is doing but it seems to be key!
Hopefully this is a solution but if there are alternate ones please let me know.

thank you
David

finding nth (5th) placed value in last 3 months

David_S

New Member

Excel Facts

scottsen

Well-known Member

David_S

New Member

scottsen

Well-known Member

David_S

New Member

David_S

New Member

David_S

New Member

Similar threads

Forum statistics

Share this page

finding nth (5th) placed value in last 3 months

David_S

New Member

Excel Facts

scottsen

Well-known Member

David_S

New Member

scottsen

Well-known Member

David_S

New Member

David_S

New Member

David_S

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock