Getting previous and current year data

[Anonymous]

Hi all,

I am trying to achieve the following table where I want to show the Rate for the current and past year

and my data is like the following

I have a slicer for users to select the year they wish to see and the table should show the rates for that year + the previous year 

(E.g. if 2025 is selected, rates for 2025 and 2024 should be shown in the table)

I have the measures 

Selected Year = SELECTEDVALUE(SPI[Year])

Incident Rate Selected Year = CALCULATE(SUM(SPI[Incidents Rate]), FILTER(SPI,'SPI'[Year] = [Selected Year])) which works 

and 

Incident Rate Previous Year = CALCULATE(SUM(SPI[Incidents Rate]), FILTER(SPI,'SPI'[Year] = SELECTEDVALUE(SPI[Year])-1))

but this measure returns blank

Any help is much appreciated!

[mdaatifraza5556 · Accepted solution]

Hi @Anonymous 

Can you please try the below DAX?

For the prev



VAR Selected_Year = SELECTEDVALUE(SPI[Year])
RETURN
CALCULATE(SUM(SPI[Incidents Rate]), 'SPI'[Year] = Selected_Year-1)

If this answers your questions, kindly accept it as a solution.

[danextian · Accepted solution]

Hi @Anonymous 

If you're meaning to just get the previous year's rate based on the current slicer selection, you can try this:

PY Rate =
CALCULATE (
    SUM ( SampleData[Incidents Rate] ),
    FILTER (
        ALL ( SampleData[Year] ),
        SampleData[Year]
            = MAX ( SampleData[Year] ) - 1
    )
)

If there is no year selected, it will return the max year - 1's value.

If the goal is to make the column name dynamic as well, a disconnected table will be needed to store all possible column names—such as Alert, Number of Incidents, and Targets—along with their corresponding sort values. Additionally, a measure will be required to determine whether to return a value based on the selected year and the values in the disconnected table. This is not a straightforward task.

[mdaatifraza5556 · Accepted solution]

Hi @Anonymous 

Can you please try the below DAX?

For the prev



VAR Selected_Year = SELECTEDVALUE(SPI[Year])
RETURN
CALCULATE(SUM(SPI[Incidents Rate]), 'SPI'[Year] = Selected_Year-1)

If this answers your questions, kindly accept it as a solution.

[pankajnamekar25]

Please try this Incident Rate Previous Year =
VAR SelectedYr = [Selected Year] 
RETURN
CALCULATE(
SUM(SPI[Incidents Rate]),
FILTER(SPI, SPI[Year] = SelectedYr - 1)
)