topic Re: RANKX and SAMEPERIODLASTYEAR in DAX Commands and Tips

RANKX and SAMEPERIODLASTYEAR

RachaelB — Wed, 24 May 2023 13:34:30 GMT

Hi all,

I'm new to the forum and would appreciate some help.

I have Filters on all pages, that the end user can choose for either date range, and/or segment. I have created a RANKX measure, which uses those filters.

I want to see which individuals are costing the most in repair costs, by segment, for the date period chosen. (The DateKey is used as there are a few different date fields in the model). This is the DAX I've written to do that:

Individual Ranked Total Repair Costs =

CALCULATE(

RANKX(ALL('Repair Data'[Individual Name]), [Total Repair Costs],,DESC),

ALLSELECTED('Repair Data'[Segment]),

ALLSELECTED('Calendar'[DateKey]))

It gives me the results I expect.

I want to compare this to the SAMEPERIODLASTYEAR, however when I try to use SAMEPERIODLASTYEAR, it's showing the previous year's costs for those ranking in the original timescale rather than giving me new individuals ranked for the previous period...

Individual Ranked Total Repair Costs LY =

CALCULATE(

RANKX(ALL('Repair Data'[Individual Name]), [Total Repair Costs],,DESC),

ALLSELECTED('Repair Data'[Segment]),

ALL('Calendar'[DateKey]),

SAMEPERIODLASTYEAR('Calendar'[DateKey]))

Any thoughts?? Thank you all!

Re: RANKX and SAMEPERIODLASTYEAR

johnt75 — Wed, 24 May 2023 14:12:10 GMT

What is the definition of [Total Repair Costs]? Is that manipulating the filter context ?

Re: RANKX and SAMEPERIODLASTYEAR

RachaelB — Wed, 24 May 2023 14:23:02 GMT

Thanks for responding!

Total Repair Costs is using a filter in it's DAX formula as I only want to include certain statuses of repairs. It's a measure that I want to display in a graph. So the graph will show the top ranked individuals in the X axis, with the costs showing as the size of the bar.

Total Repair Costs =

CALCULATE(

SUM('Repair Data'[Repairs Total]),

USERELATIONSHIP('Repairs Data'[Date1],'Calendar'[DateKey]),

FILTER('Repair Data','Repair Data'[Repairs Completed]="Closed")

)

(FYI, Repairs Total is a field in dataset and not calculated in anyway in pbix)

Re: RANKX and SAMEPERIODLASTYEAR

johnt75 — Wed, 24 May 2023 14:42:24 GMT

Try

Individual Ranked Total Repair Costs LY = CALCULATE ( RANKX ( ALL ( 'Repair Data'[Individual Name] ), [Total Repair Costs],, DESC ), ALLSELECTED ( 'Repair Data'[Segment] ), ALLSELECTED ( 'Calendar' ), SAMEPERIODLASTYEAR ( 'Calendar'[DateKey] ) )

Re: RANKX and SAMEPERIODLASTYEAR

RachaelB — Wed, 24 May 2023 15:11:26 GMT

That's great, thank you so much!