Getting the previous value of a measure using Date(year) as a criteria

[RochelReyes]

Hi Everyone,

I would like to ask for your help on computing for the previous value of a measure.
I have a table that looks like this:

To give context on this, the values above are base on the total score that are our exhibitors per event year are giving us which has the following formula 

NPS_score = ((CALCULATE(DISTINCTCOUNT(research_kpi[responseid]), research_kpi[NPSgroup] = "Promoters (9-10)")

                / CALCULATE(DISTINCTCOUNT(research_kpi[responseid]), research_kpi[NPSgroup] <> BLANK()))

            -

        (CALCULATE(DISTINCTCOUNT(research_kpi[responseid]), research_kpi[NPSgroup] = "Detractors (0-6)")

                 / CALCULATE(DISTINCTCOUNT(research_kpi[responseid]), research_kpi[NPSgroup] <> BLANK()))

) * 100

What I would like to attain is the previous value of the previous year just like this table:

I tried using this formula

PreviousNPSScore = CALCULATE([NPS_score], FILTER(research_kpi, research_kpi[year] = PREVIOUSYEAR(research_kpi[year])))
but I am getting no values at all.

May I know if there is a workaround on this?

Thank you.

[AlexisOlson · Accepted solution]

This looks like a good candidate for OFFSET.

Try this:

PreviousNPSScore =
CALCULATE (
    [NPS_score],
    OFFSET (
        -1,
        ORDERBY ( research_kpi[Year], ASC )
    )
)

[RochelReyes]

Thank you so much for this. This has been solved😊