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

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    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])
    )

    • romovaro's avatar
      romovaro
      Responsive Resident
      1 year ago

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

    • romovaro's avatar
      romovaro
      Responsive Resident
      1 year ago

      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's avatar
        bhanu_gautam
        Super User
        1 year ago

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

  • 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,