The current week's ranking

[Anonymous]

Hi all,

I have a data model that consists of two tables: "Data" and "DATES". In the data model there's a column called "Revenue" and a column called "Dealstate (Progress)". I have created a measure that sums up EMEA revenue where the DealState (Progress) = "Won". That measure is:

EMEA Won MRR With Access = CALCULATE(SUM('Data'[Revenue], 'Data'[DealState (Progress)] = "Won")

I have a page that looks at a weekly view. Each week has a number associated with it in the Dates table, the column is called 'Week' and is based on the WEEKNUM() function.

I need to create measure that:
Creates a rank for each week based on the value of the "EMEA Won MRR With Access" measure, with the highest rank (1) being the week with the highest value of EMEA Won MRR With Access. I then need to be able to output the current week's ranking on a card visual.

For example, if there's data like this in the DATA table (Date/Revenue)

03/01/2022	100
07/01/2022	250
11/01/2022	60
12/01/2022	10
25/01/2022	90
26/01/2022	500

We can see there are 3 weeks of data (although there are technically 6 'weeks' in Jan 2022) so the DATES table would go up to week 6 in Jan:

If we assume that the data ends at the end of January, so we have 6 weeks to rank, in the above example, there are 3 weeks with data and 3 weeks without, so the real ranking would look something like this:

Now let's think bigger. Let's imagine it's August 1st - I'm in week 32, and I don't want a table of 32 ranks, I just want a card value that says the current week's rank number. This will always be the current (maximum) week as it'll form part of a weekly report. And we can also assume that no weeks will ever tie in rank so we don't need to worry about errors.

I've tried playing with various SUMX and SUMMARIZE measures, but have had no luck. 

I would appreciate any help at all, thank you.

[lbendlin]

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523