RANKX() - Rank at First Month and Last Month of a Custom Time Period Selection

[Anonymous]

Hi all, 

Given the following sample data, along with a timeline slicer which I can select Months such as Jan-Mar, Feb-Apr, Jan-Apr

Month	Customer	Sales
January	A	10
January	B	23
January	C	34
February	A	12
February	B	31
February	C	29
March	A	9
March	B	17
March	C	13
April	A	21
April	B	13
April	C	14

How do I use Rankx() or a suitable measure to generate the following visual?

For timeline Jan-Mar

Customer	Total Sales for Period Selected	Rank at Starting Month of Period Selected	Current Rank - Rank at Latest Month of Period Selected
A	31	3	3
B	71	2	1
C	76	1	2

Here, Starting Month of Period Selected = Jan

Latest Month of Period Selected = Mar

Any advise is greatly appreciated

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You could use Max() and Min() function to calculate the value of max month and min month of time period.

minvalue = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Customer]=SELECTEDVALUE('Table'[Customer])&&'Table'[month]=MIN('Table'[month])))

maxvalue = CALCULATE(SUM('Table'[Sales]),FILTER('Table','Table'[Customer]=SELECTEDVALUE('Table'[Customer])&&'Table'[month]=MAX('Table'[month])))

Then use RANKX() function to rank the values.

Best Regards,

Jay

[vanessafvg]

are you using text for the dates or can you provide the data with proper dates?

[Anonymous]

Dear @vanessafvg,

The dates are in the proper date/time format in the original dataset.

I summarized it according to the month name just for the sample data provided above