Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Getting previous and current year data

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!

 

 

 

Need Help
  • mdaatifraza5556's avatar
    mdaatifraza5556
    1 year ago

    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's avatar
    danextian
    1 year ago

    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.

3 Replies

  • pankajnamekar25's avatar
    pankajnamekar25
    Super User
    1 year ago

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

  • mdaatifraza5556's avatar
    mdaatifraza5556
    Super User
    1 year ago

    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's avatar
    danextian
    Super User
    1 year ago

    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.