Track NPS Status within Years.

romovaro First, you need to create a calculated column that captures the status of each customer in the previous fiscal year

DAX
PreviousYearStatus =
VAR CurrentFY = 'ResponsesFY25'[Fiscal Year]
VAR PreviousFY = CurrentFY - 1
RETURN
CALCULATE(
MAX('ResponsesFY25'[Event Score Status]),
FILTER(
'ResponsesFY25',
'ResponsesFY25'[Client Account Name] = EARLIER('ResponsesFY25'[Client Account Name]) &&
'ResponsesFY25'[Fiscal Year] = PreviousFY
)
)

Next, create a calculated column that describes the movement from the previous fiscal year to the current fiscal year.

DAX
MovementDescription =
VAR CurrentStatus = 'ResponsesFY25'[Event Score Status]
VAR PreviousStatus = 'ResponsesFY25'[PreviousYearStatus]
RETURN
SWITCH(
TRUE(),
PreviousStatus = "Detractor" && CurrentStatus = "Passive", "Detractor to Passive",
PreviousStatus = "Detractor" && CurrentStatus = "Promoter", "Detractor to Promoter",
PreviousStatus = "Passive" && CurrentStatus = "Promoter", "Passive to Promoter",
PreviousStatus = "Promoter" && CurrentStatus = "Passive", "Promoter to Passive",
PreviousStatus = "Promoter" && CurrentStatus = "Detractor", "Promoter to Detractor",
PreviousStatus = "Passive" && CurrentStatus = "Detractor", "Passive to Detractor",
"No Change"
)

Finally, you can create a summary table or visualization to display the movements. For example, you can use a matrix visualization in Power BI to show the count of customers for each type of movement.

DAX
MovementSummary =
SUMMARIZE(
'ResponsesFY25',
'ResponsesFY25'[Fiscal Year],
'ResponsesFY25'[MovementDescription],
"CustomerCount", COUNT('ResponsesFY25'[Client Account Name])
)