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

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