How to bypass filter using ALLEXCEPT but used inside RANKX and CALCULATE

[Anonymous]

This is my dataset:

Seller Name	Weekend	Week Rank	Sales
Ben	28/12/2019	4	40
Tom	28/12/2019	4	30
Jason	28/12/2019	4	10
Clair	28/12/2019	4	20
Ben	4/01/2020	3	10
Tom	4/01/2020	3	20
Jason	4/01/2020	3	10
Clair	4/01/2020	3	30
Ben	11/01/2020	2	20
Tom	11/01/2020	2	30
Jason	11/01/2020	2	20
Clair	11/01/2020	2	30
Ben	18/01/2020	1	20
Tom	18/01/2020	1	10
Jason	18/01/2020	1	0
Clair	18/01/2020	1	20

I want to find the ranking of each seller based on the sum of their sales for the current fortnight, and then a ranking based on the sum of their sales for the previous fortnight. Eg. my desired output table is:

Seller Name	Current fortnight sales rank	Previous fortnight sales rank
Ben	2	1
Tom	3	3
Jason	4	4
Clair	1	2

The tricky part is there is a filter on the table which limits the weekends to the 2 most recent weeks. This is compulsory because there are other things in the table which MUST BE reported only for the 2 most recent weeks. So I need to bypass this filter to get the previous fortnight sales rank. 

I have created a measure for Current fortnight sales rank which works fine:

Current fortnight sales rank = 

RANKX(

   ALLSELECTED(Table1[Seller Name]),

   CALCULATE(SUM(Table1[Sales]),Table1[Week Rank]<3))

But my previous fortnight sales rank is not working....

Previous fortnight sales rank = 

RANKX(

    ALLSELECTED(Table1[Seller Name]),

    CALCULATE(SUM(Table1[Sales]),ALLEXCEPT(Table1,Table1[Seller Name]),Table1[Week Rank]<5&&Table1[Week Rank]>2)

)

My previous fortnight sales rank is giving me funny ranking that are incorrect.

Any help would be appreciated.......

[edhans · Accepted solution]

Based on your data, the following seems to work:

Previous Fortnight Sales Rank =
RANKX(
    ALLSELECTED( Seller[Seller Name] ),
    CALCULATE(
        SUM( Seller[Sales] ),
        Seller[Week Rank] = 3 || Seller[Week Rank] = 4
    ),
    ,
    DESC,
    DENSE
)

Everyone has 50 except Jason, so it is 1,1,1,4, but by using the DENSE option, it becomes 1,1,1,2 where 2 & 3 are not skipped.

[amitchandak]

Please find the solution at

https://www.dropbox.com/s/0ec5u2nhxuo22eb/weeklysellerRank.pbix?dl=0

You might have to do a small change to adjust the fortnight calculations

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin