topic Switch between two dynamic years to check the rank of revenues based on customers. in DAX Commands and Tips

Switch between two dynamic years to check the rank of revenues based on customers.

ribs — Fri, 02 May 2025 07:33:46 GMT

Hi,

I am on the quest of finding out top n Customers (n- desired input value by the user; default is 200) between any two years revenue for example(2025 and 2024) and it should dynamically change based the users's input. Is this possible?

Customer	2024	2025	Rank Compare from 2024 with 2025
A	90	50	1
B	50	60	2
C	40	40	3
D	10	20	4
E	5	70	5

Customer	2024	2025	Rank Compare from 2025 with 2024
E	5	70	1
B	50	60	2
A	90	50	3
C	40	40	4
D	10	20	5

I need the above two tables to be a single table and the years must be dynamically choosen. I have created following measures:
Revenue current year; Revenues previous year; Revenue previous year-1; Revenues previous year-2
I was able to implement dynamic TopN numbers with the parameter feature.

Any help would be appreciated.

Thanks,

Re: Switch between two dynamic years to check the rank of revenues based on customers.

johnbasha33 — Fri, 02 May 2025 15:27:30 GMT

ribs

Yes, this is absolutely possible in Power BI using DAX and a parameterized approach! From your description, you've already done a lot of the groundwork: you’ve created revenue measures for different years and implemented a dynamic Top N filter using a parameter. What you're missing is combining both the dynamic year comparison and ranking logic into a single flexible table.

Here’s how to get there:

Step 1: Create a Year Selection Table

You need a disconnected table (not related to your date table) for the user to choose the “Compare From” and “Compare To” years.

Example:

CompareYears =
ADDCOLUMNS(
CALENDAR(DATE(2020,1,1), DATE(2030,12,31)),
"Year", YEAR([Date])
)

Then remove the Date column and keep only the distinct years.

Create two parameters:

FromYear

ToYear

Step 2: Create Revenue Measures

Assuming your date table is properly marked as a date table and relationships are active:

Revenue Selected From Year =
CALCULATE(
[Total Revenue],
'Date'[Year] = SELECTEDVALUE(CompareYears[Year], 2024) // Replace with your FromYear parameter
)

Revenue Selected To Year =
CALCULATE(
[Total Revenue],
'Date'[Year] = SELECTEDVALUE(CompareYears[Year], 2025) // Replace with your ToYear parameter
)

Step 3: Create Rank Measure

Customer Rank =
RANKX(
ALLSELECTED(Customer[CustomerName]),
[Revenue Selected To Year] - [Revenue Selected From Year],
,
DESC,
Dense
)

Step 4: Create the Final Table

In your matrix or table visual:

Use Customer[CustomerName] as rows.
Add these measures as columns:
- [Revenue Selected From Year]
- [Revenue Selected To Year]
- [Customer Rank]

Apply the Top N filter using your parameter on the [Customer Rank] measure.

Optional – Add Sorting Logic

To make it more intuitive, add a slicer that lets users pick whether to sort by:

Increase (ToYear > FromYear)
Decrease (ToYear < FromYear)

Then use conditional logic inside the RANKX() measure to adjust accordingly.

Bonus Tip

If you want all combinations (2024 vs 2025, 2025 vs 2024, etc.) pre-generated instead of user-selected, you'd use a calculated table with combinations and create a matrix from that. But your dynamic approach is more interactive and cleaner.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!

Re: Switch between two dynamic years to check the rank of revenues based on customers.

v-sdhruv — Mon, 05 May 2025 07:14:52 GMT

Hi ribs ,
Just wanted to check if you had the opportunity to review the solution provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You

Re: Switch between two dynamic years to check the rank of revenues based on customers.

ribs — Mon, 05 May 2025 11:56:33 GMT

johnbasha33
Creating parameters for From and To was a great idea. Although I had to tweak the RankX function a bit with switch like below:

Dynamic Rank =

VAR SelectYear= SELECTEDVALUE(RankYearSelector[Year])

RETURN

SWITCH(

TRUE();

SelectYear== 'From'[From Value];RANKX(ALLSELECTED(Customer[Name]); [Dynamic Revenue 1];;DESC;Dense);

SelectYear== 'To'[To Value]; RANKX(ALLSELECTED(Customer[Name]);[Dynamic Revenue 2];;DESC;Dense)

)

It indeed worked!
Thank you!