DAX to compare two Revenue Year columns with RannkX function

[ribs]

Hello,

I am trying to build a matrix with the customers column and two revenue columns for current year and previous year respectively and based on the user's input through a slicer on year. It should calculate Rank for that Year

Rank = var CurrentYear = RANKX(ALLSELECTED(Customer[Name]); [Revenue Current];;DESC;Dense) var PreviousYear = RANKX(ALLSELECTED(Customer[Name]); [Revenue Previous];;DESC;Dense) RETURN if(SELECTEDVALUE(GLEntries[PostingDate].[Year])=2025;CurrentYear;PreviousYear)

Below DAX is working but it shows results for only the selected year and for the other year blank is displayed.
How to solve this issue?

Customer	Revenue Current	Revenue Previous	Rank (from 2024 to 2025 or from 2025 to 2024)
A	90	50	1
B	50	60	2
C	40	40	3
D	10	20	4
E	5	70	5

[ribs · Accepted solution]

@v-pnaroju-msft Thanks for your reply!
I would like to let you know that the issue was resolved. The problem was that I was using the year column that was connected to the sales table. I should use a disconnected column or a table for the slicer.
However, I checked your report. The output seems to be the same issue I was running into when I click on the Year slicer 2024, the Revenue Previous column is blank, but for 2025 it works fine

[v-pnaroju-msft]

Thankyou, @bhanu_gautam, for your response.

Hi @ribs,

We appreciate your inquiry on the Microsoft Fabric Community Forum.

Please find attached the screenshot and PBIX file, which may help in resolving the issue:

If you find our response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members who may have similar queries.

Thank you.

[ribs]

@bhanu_gautam No luck!😕

[ribs]

Hallo,

Thanks for your response.
If you could see my message, I have written the exact same code. The issue still persists. If Year 2025 is selected that year revenue is displayed and the previous year is blank

[bhanu_gautam]

@ribs  Use MAX(GLEntries[PostingDate].[Year]) instead of SELECTEDVALUE(GLEntries[PostingDate].[Year])

Revenue Current Year Measure:

DAX
Revenue Current =
CALCULATE(
SUM(GLEntries[Revenue]),
GLEntries[PostingDate].[Year] = MAX(GLEntries[PostingDate].[Year])
)

Revenue Previous Year Measure:

DAX
Revenue Previous =
CALCULATE(
SUM(GLEntries[Revenue]),
GLEntries[PostingDate].[Year] = MAX(GLEntries[PostingDate].[Year]) - 1
)

Rank Measure:

DAX
Rank =
VAR CurrentYear = RANKX(
ALLSELECTED(Customer[Name]),
[Revenue Current],
,
DESC,
DENSE
)
VAR PreviousYear = RANKX(
ALLSELECTED(Customer[Name]),
[Revenue Previous],
,
DESC,
DENSE
)
RETURN
IF(
SELECTEDVALUE(GLEntries[PostingDate].[Year]) = MAX(GLEntries[PostingDate].[Year]),
CurrentYear,
PreviousYear
)

[bhanu_gautam]

@ribs 

Create a Measure for Revenue Current Year:

DAX
Revenue Current =
CALCULATE(
SUM(GLEntries[Revenue]),
GLEntries[PostingDate].[Year] = SELECTEDVALUE(GLEntries[PostingDate].[Year])
)

Create a Measure for Revenue Previous Year:

DAX
Revenue Previous =
CALCULATE(
SUM(GLEntries[Revenue]),
GLEntries[PostingDate].[Year] = SELECTEDVALUE(GLEntries[PostingDate].[Year]) - 1
)

Measure for Rank:

DAX
Rank =
VAR CurrentYear = RANKX(
ALLSELECTED(Customer[Name]),
[Revenue Current],
,
DESC,
DENSE
)
VAR PreviousYear = RANKX(
ALLSELECTED(Customer[Name]),
[Revenue Previous],
,
DESC,
DENSE
)
RETURN
IF(
SELECTEDVALUE(GLEntries[PostingDate].[Year]) = 2025,
CurrentYear,
PreviousYear
)

Add Customer[Name] to the rows.
Add Revenue Current, Revenue Previous, and Rank measures to the values.