Track NPS Status within Years.

[romovaro]

HI All

I have a table with FY, customer names and a filter showing if during the year they were Detractors, Passive or Promoters

Formulas used:

Client_Scores Event =

CALCULATE(

    AVERAGE('ResponsesFY25'[LTR - Event]),

    ALLEXCEPT('ResponsesFY25','ResponsesFY25'[Client Account Name], ResponsesFY25[Fiscal Year])

)

Event Score Status =

IF('ResponsesFY25'[Client_Scores Event] <= 6.99 , "Detractor",

IF('ResponsesFY25'[Client_Scores Event] <= 8.99, "Passive",

IF('ResponsesFY25'[Client_Scores Event] <= 10, "Promoter",

"TBD"

)))

NOw I would like to track all movements within fiscal years

FRom FY23 to FY24 and from FY24 to FY25

From Detractor to Passive

From Passive to Promoter

From Detractor to Promoter

From Promoter to Passive

From Promoter to Detractor

From Passive to Detractor.

Any suggestions? thanks.

[DataNinja777]

Hi @romovaro ,

To track customer status movements between fiscal years such as FY23 to FY24 and FY24 to FY25, you can create a calculated table in Power BI that compares each client's NPS category year over year. Assuming you've already assigned each client a status like "Detractor", "Passive", or "Promoter" per fiscal year using your [Event Score Status] formula, you can summarize these transitions using the following DAX:

NPS_Transitions = 
VAR AllYears =
    ADDCOLUMNS (
        VALUES ( 'ResponsesFY25'[Client Account Name] ),
        "FY23", CALCULATE ( MAX ( 'ResponsesFY25'[Event Score Status] ), 'ResponsesFY25'[Fiscal Year] = "FY23" ),
        "FY24", CALCULATE ( MAX ( 'ResponsesFY25'[Event Score Status] ), 'ResponsesFY25'[Fiscal Year] = "FY24" ),
        "FY25", CALCULATE ( MAX ( 'ResponsesFY25'[Event Score Status] ), 'ResponsesFY25'[Fiscal Year] = "FY25" )
    )
RETURN
    SELECTCOLUMNS (
        ADDCOLUMNS (
            AllYears,
            "Transition FY23_FY24", [FY23] & " → " & [FY24],
            "Transition FY24_FY25", [FY24] & " → " & [FY25]
        ),
        "Client", [Client Account Name],
        "Transition FY23_FY24", [Transition FY23_FY24],
        "Transition FY24_FY25", [Transition FY24_FY25]
    )

This DAX creates a new table where each row represents a client and includes the transitions in status between FY23–FY24 and FY24–FY25. You can then use this table in visuals to count how many clients changed from one category to another, like "Detractor → Promoter". You can filter or group by these transitions in a matrix or bar chart to get a quick view of sentiment improvement or deterioration over time.

Best regards,

[bhanu_gautam · Accepted solution]

@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])
)

[romovaro]

hi @bhanu_gautam 

In case I also needed to add the year (previous to previous) ex FY23 (Prev example was for FY25 and FY24).

MovementDescription =
VAR CurrentStatus = 'ResponsesFY25'[Event Score Status]
VAR PreviousStatus = 'ResponsesFY25'[PreviousYearStatus]

VAR Pre-Previous Status ? 
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"
)

[bhanu_gautam]

@romovaro ,Create a calculated column to capture the status of each customer two fiscal years ago:

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

Modify the MovementDescription calculated column to include the status from two fiscal years ago:

DAX
MovementDescription =
VAR CurrentStatus = 'ResponsesFY25'[Event Score Status]
VAR PreviousStatus = 'ResponsesFY25'[PreviousYearStatus]
VAR PrePreviousStatus = 'ResponsesFY25'[PrePreviousYearStatus]
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"
)

If you want to include movements from two fiscal years ago in the MovementDescription, you can extend the logic:

DAX
MovementDescription =
VAR CurrentStatus = 'ResponsesFY25'[Event Score Status]
VAR PreviousStatus = 'ResponsesFY25'[PreviousYearStatus]
VAR PrePreviousStatus = 'ResponsesFY25'[PrePreviousYearStatus]
RETURN
SWITCH(
TRUE(),
PrePreviousStatus = "Detractor" && PreviousStatus = "Passive" && CurrentStatus = "Promoter", "Detractor to Passive to Promoter",
PrePreviousStatus = "Detractor" && PreviousStatus = "Promoter" && CurrentStatus = "Passive", "Detractor to Promoter to Passive",
PrePreviousStatus = "Passive" && PreviousStatus = "Promoter" && CurrentStatus = "Detractor", "Passive to Promoter to Detractor",
-- Add more conditions as needed
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"
)

[romovaro]

Thanks @bhanu_gautam 

Formula works and I get the movements. thanks.

for the last formula, I get the error : The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

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