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

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

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's avatar
bhanu_gautam
Icon for Super User rankSuper User
1 year ago

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
)