Forum Discussion

romovaro's avatar
romovaro
Responsive Resident
1 year ago
Solved

Track NPS Status within Years.

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(  ...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

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

DataNinja777's avatar
DataNinja777
Super User
1 year ago

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,