Transition from SELECTEDVALUES to a Dynamic Filter

[Anonymous]

Goodmorning,

The situation I have is that I'm ranking Sales Persons by Total Sales and then taking their previous month's rank and comparing it to the current month. The way I've done this is in the formulas below.

However, I'm looking for help transitioning my current formula to a more dynamic format. I would prefer to have this all on one table/matrix, but my current set up forces me to create different tables because I'm using the SELECTEDVALUE function. I tried making a generic "Previous Month Rank" measure but couldn't due to a table error. 

Any suggestions would be great!

Thank you!

Sales Rank =
IF( HASONEVALUE( 'Sales Index'[Sales Person] ),
RANKX(
ALL( 'Sales Index'[Sales Person] ) ,
'Sales Data'[Sales], ,
DESC
),
BLANK())

Rank Change =
Var CurrentRank = [Sales Rank]
Var PreviousRank =
IF(SELECTEDVALUE('Sales Data'[Month]) = "February",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "January" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "March",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "February" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "April",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "March" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "May",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "April" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "June",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "May" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "July",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "June" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "August",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "July" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "September",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "August" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "October",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "September" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "November",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "October" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "December",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "November" }, ALL('Sales Data'[Month No.])),
IF(SELECTEDVALUE('Sales Data'[Month]) = "January",
CALCULATE(
[Sales Rank],
'Sales Data'[Month] IN { "December" }, ALL('Sales Data'[Month No.]))," "
) ) ) ) ) ) ) ) ) ) ) )

Return

CALCULATE(PreviousRank - CurrentRank)

[v-frfei-msft]

Hi @Anonymous,

Kindly share your sample data to me.

Regards,

Frank