RANKX amatuer

[johnmelbourne]

Hi

I have a datset like this

and I want to produce this with a slicer / filter ? for date 

using 30/9/2018

Similarly if I choose 30/12/2018 I should ge this ....

I just can't seem to work it out at the moment. I keep getting an answer of, for example 4!

So confused.

I don't particularly want to hard code a filter into the DAX, and would rather use a drop down slicer if possible. Any clues or assistance would be appreciated.

Thanks

John

[Ashish_Mathur · Accepted solution]

Hi,

Create a Calendar Table and build a relationship from the EndDate column of your Table to the Date column of the Calendar Table.  To your visual/slicer, drag the Date column from the Calendar Table.  To your Table visual, drag the Company column.  Write these measures

Total = SUM(Data[ColumnA])

Rank = RANKX(ALL(Data[Company]),[Total])

Hope this helps.

[johnmelbourne]

Since that worked so well @Ashish_Mathur ..... would you have any hints about a solution for the above where I had one measure for rank this quarter, and then another column (measure) for rank last quarter? So I can show the movement of rank between quarters?

Any assistance would be greatly appreciated.

---

---

---

@Ashish_Mathur wrote:

Hi,

Create a Calendar Table and build a relationship from the EndDate column of your Table to the Date column of the Calendar Table.  To your visual/slicer, drag the Date column from the Calendar Table.  To your Table visual, drag the Company column.  Write these measures

Total = SUM(Data[ColumnA])

Rank = RANKX(ALL(Data[Company]),[Total])

Hope this helps.

---

---

[Ashish_Mathur]

Hi,

In the Calendar Table, create a Quarter column as well.  Drag the quarter column to your slicer/filter and select any quarter.  Write this measure

Rank in previous quarter = CALCULATE([Rank],PREVIOUSQUARTER(Calendar[Date]))

Hope this helps.

[johnmelbourne]

Many thanks for taking the time to respond. Your solution is perfect. So "simple", so beautiful. 

[Ashish_Mathur]

Thank you.