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

RochelReyes — Mon, 12 Feb 2024 10:45:45 GMT

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.

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

AlexisOlson — Mon, 12 Feb 2024 17:17:05 GMT

This looks like a good candidate for OFFSET.

Try this:

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

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

RochelReyes — Tue, 13 Feb 2024 13:03:07 GMT

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

topic Re: Getting the previous value of a measure using Date(year) as a criteria in DAX Commands and Tips

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

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

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