Forum Discussion

ribs's avatar
ribs
Icon for Helper I rankHelper I
1 year ago
Solved

DAX 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 s...
  • ribs's avatar
    ribs
    1 year ago

    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

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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.