Forum Discussion

RachaelB's avatar
RachaelB
Frequent Visitor
3 years ago
Solved

RANKX and SAMEPERIODLASTYEAR

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!
RANKX
Sameperiodlastyear
  • johnt75's avatar
    johnt75
    3 years ago

    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] )
)

4 Replies

  • johnt75's avatar
    johnt75
    Icon for Super User rankSuper User
    3 years ago

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

    • RachaelB's avatar
      RachaelB
      Frequent Visitor
      3 years ago

      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)
      • johnt75's avatar
        johnt75
        Icon for Super User rankSuper User
        3 years ago

        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] )
)