RANKX WITH A DATE COLUMN

[Anonymous]

Hi everyone 🙂

Hope you guys can help me explain how the function really works.

I have a draft dataset as below. And I need to rank the date (1st rank for the latest date) both by shop and by status (this means the rank will reset after we change to other subsets)

I used the dax function:

RANK2 =

RANKX(ALLEXCEPT(Sheet1,Sheet1[SHOPCODE],Sheet1[STATUS]),CALCULATE(SELECTEDVALUE(Sheet1[DATE])))

Honestly, I found the solution by luck, but I really want to understand how the context works. There are some questions that I am unclear about the answer:

• How the 'selected value' function works in this context. If i don't use this function, is there any alternative way? I believe that if the column "date" is replaced by another column with number (ex: sales amount) then I can use calculate(sum(Sheet1[Sales Amount]), which is understandable to me in this case.

• If I remove calculate function, the whole function will become: 

  RANK2 =

  RANKX(ALLEXCEPT(Sheet1,Sheet1[SHOPCODE],Sheet1[STATUS]),SELECTEDVALUE(Sheet1[DATE]))

  .Then the rank results return 1 for all rows and I don't really get it. How does the calculate function help in this case?

 I am just a newbie to DAX and hope you guys can help give me a solid explanation. Much appreciated for your help 🙂