Forum Discussion
Helper IDAX to compare two Revenue Year columns with RannkX function
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 |
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
6 Replies
- bhanu_gautam
Super User
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. 
ribsHallo,
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
)
- ribs
bhanu_gautam No luck!😕
v-pnaroju-msftCommunity Support
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
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
