Forum Discussion

oldnapkin's avatar
oldnapkin
Regular Visitor
9 years ago
Solved

Need help with RANKX within a date range

I want to numerically rank an amount within a specific date. Say my data is this.   Person Sales Date Bob 10 January Jeff 20 January Steve 30 January Bob 50 February Jeff 40 February Steve 30 Febru...
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    oldnapkin,

     

    MonthRank = RANKX(FILTER(Sales,Sales[Date]=EARLIER(Sales[Date])),Sales[Sales],,DESC)

    YearRank = RANKX(Sales,CALCULATE(SUM(Sales[Sales]),ALLEXCEPT(Sales,Sales[Person])),,DESC,Dense)

     

    Regards,

    Charlie Liao

v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
9 years ago

oldnapkin,

 

MonthRank = RANKX(FILTER(Sales,Sales[Date]=EARLIER(Sales[Date])),Sales[Sales],,DESC)

YearRank = RANKX(Sales,CALCULATE(SUM(Sales[Sales]),ALLEXCEPT(Sales,Sales[Person])),,DESC,Dense)

 

Regards,

Charlie Liao