topic Re: DAX to compare two Revenue Year columns with RannkX function in DAX Commands and Tips

DAX to compare two Revenue Year columns with RannkX function

ribs — Fri, 02 May 2025 09:44:52 GMT

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

Re: DAX to compare two Revenue Year columns with RannkX function

bhanu_gautam — Fri, 02 May 2025 09:55:31 GMT

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.

Re: DAX to compare two Revenue Year columns with RannkX function

ribs — Fri, 02 May 2025 11:02:27 GMT

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

Re: DAX to compare two Revenue Year columns with RannkX function

bhanu_gautam — Fri, 02 May 2025 11:09:03 GMT

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:

Re: DAX to compare two Revenue Year columns with RannkX function

ribs — Fri, 02 May 2025 11:21:36 GMT

bhanu_gautam No luck!😕

Re: DAX to compare two Revenue Year columns with RannkX function

v-pnaroju-msft — Mon, 05 May 2025 08:34:36 GMT

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.

Re: DAX to compare two Revenue Year columns with RannkX function

ribs — Mon, 05 May 2025 12:28:21 GMT

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