NPS last score per period

[Tazmastablasta]

Hi All 

I have a requirement to create a measure that calculates NPS based on the last score per period per customer (one vote per customer  either per month or per quarter), so if the line chart shows a month then last score per month if it shows the quarter then the quarter. (Chart below) 

This is what I came up with :

NPS = 
var NumOfRespon= 
SUMX(LASTDATE(VALUES('Fiscal Calendar'[Date])),
CALCULATE (DISTINCTCOUNT(  'NPS'[ID] ),
    FILTER ( 'NPS', 'NPS'[Score] <> BLANK () || 'NPS'[Score] = 0 )
))
var _promoters = 
SUMX(LASTDATE(VALUES('Fiscal Calendar'[Date])),
CALCULATE(DISTINCTCOUNT('NPS'[ID]),FILTER( 'NPS', 'NPS'[NPS label]="Promoters")))

var _detractors=
SUMX(LASTDATE(VALUES('Fiscal Calendar'[Date])),
CALCULATE(DISTINCTCOUNT('NPS'[ID]),FILTER('NPS','NPS'[NPS label]="Detractors")))

RETURN 
((_promoters/NumOfRespon)-(_detractors/NumOfRespon)) *100

So the var NumOfRespon work exactly as expected so if the chart is on month level then values in calculation are from the last date in the month per customer, if the graph is on Fiscal quarter then the values are from the quarter.

The problem starts with var _promoters and var _detractors.

I use a column NPS label as filter for the calculation but in this case what happens is the values are filtered first by the label and then the distinct count happens. Like so 

'NPS', 'NPS'[NPS label]="Promoters" ----> LASTDATE(VALUES('Fiscal Calendar'[Date]) -----> DISTINCTCOUNT('NPS'[ID]).

So the var _promoters returns distinct count of only promoters ignoring any other label even if the date was after the "Promoter" score.

My question is how do I change the filter context so the calculation is like this 

 LASTDATE(VALUES('Fiscal Calendar'[Date])  ----> 'NPS', 'NPS'[NPS label]="Promoters" ----> DISTINCTCOUNT('NPS'[ID]

So the distinct count of the last scores as a base value from witch I can subset an count of Promoters , Detractors and Passive votes.

This is how the table looks like.

ID	Response Date	Score	NPS label
235235	Tuesday, July 16, 2019	10	Promoters
2222	Tuesday, July 16, 2019	10	Promoters
77789	Tuesday, July 16, 2019	10	Promoters
345345	Tuesday, July 16, 2019	9	Promoters
4454674	Tuesday, July 16, 2019	9	Promoters
345345	Tuesday, July 16, 2019	9	Promoters
565656	Tuesday, July 16, 2019	8	Passives
54343	Tuesday, July 16, 2019	8	Passives
780790	Tuesday, July 16, 2019	8	Passives
345346	Tuesday, July 16, 2019	8	Passives

I would appreciate any help, thank you for your time.

[v-juanli-msft]

Hi @Tazmastablasta 

You could combine my measures below to one.

last date = CALCULATE(LASTDATE('calendar'[Date]),FILTER(Sheet3,NOT(ISBLANK(Sheet3[Score]))))

NumOfRespon =
CALCULATE (
    DISTINCTCOUNT ( Sheet3[ID] ),
    FILTER (
        Sheet3,
        NOT ( ISBLANK ( [Score] ) )
            && Sheet3[Response Date] = Sheet3[last date]
    )
)


_promoters =
CALCULATE (
    DISTINCTCOUNT ( Sheet3[ID] ),
    FILTER (
        Sheet3,
        NOT ( ISBLANK ( [Score] ) )
            && Sheet3[Response Date] = Sheet3[last date]
            && [NPS label] = "Promoters"
    )
)


_detractors =
CALCULATE (
    DISTINCTCOUNT ( Sheet3[ID] ),
    FILTER (
        Sheet3,
        NOT ( ISBLANK ( [Score] ) )
            && Sheet3[Response Date] = Sheet3[last date]
            && [NPS label] = "Detractors"
    )
)


Final output = (([_promoters]/[NumOfRespon])-([_detractors]/[NumOfRespon]))

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tazmastablasta]

Hi @v-juanli-msft 

Thank you so much for your replay, unfortunately your solution has the same problem as mine.

I made a slight change to the data set in your PBIX dataset.

Now in August ID 21 has two scores , one passive and one promoter, and as in my calculation the NumOfResponses is showing the correct value ( one last score regardless of the label ( promoter , detractor , passive) where the measure for either Passive,Promoter,Detractor is showing the last score per label not the last score overall.

So in this example in August there is 24 distinct scores form the users ( two from ID 21 but NumOfResponses calculates this correctly as we want only the last one) but the other measures are adding to 25 which means that the ID 21 is counted twice , once as a promoter and once as a passive. So the filter context is similar to mine where label -> last date -> count .

What I am tying to do is last date values (in this example 24) and then the count of the labels from that (example .Promoter 9 , Detractor 6 , Passive 9 = 24 ) 

I am attaching your pbix file witch my changes 

https://www.dropbox.com/s/17tyz6cilx930cj/NPS%20last%20score%20per%20period.pbix?dl=0 

@amitchandak  

[amitchandak]

Try Tried, but not able to match logic. Please see this screen shot. Can you explain by checking lines, maybe in excel,

What is considered as the promoter for Aug, Sep, and Q3. And final calculation

[Tazmastablasta]

Hi @amitchandak 

I have simplified the example 

Let's say in October we have this:

The condition is to only consider the last score per ID , so in this case it will 3 promoters.

Now both my mine and @v-juanli-msft  solutions are counting last Promoter,Detractor or Passive depending on a label,

which in the October example gives the total of 7 ( 3 Promoters ,2 passive , 2 Detractors) which is wrong 

what I am trying to achieve is count of labels which where last scores.

In terms of the Quarter  the result should be, 3 Detractors, 2 Passive,  3 Promoters.

Now in both examples ( mine and @v-juanli-msft ) the distinct count of id with any label filters works well

NumOfRespon = 
SUMX(LASTDATE(VALUES('Calendar'[Date])),
CALCULATE (DISTINCTCOUNT(  'NPS'[ID] ),
    FILTER ( 'NPS', 'NPS'[Score] <> BLANK () || 'NPS'[Score] = 0 )
))

and return the desired value but  when we try to count the labels we have problems. 

Here is the link another version of PBIX 

https://www.dropbox.com/s/vbm6pndnengdgox/NPS%20last%20score.pbix?dl=0 

[amitchandak]

Please share expected values for the count of labels, response for Month and QTR

[Tazmastablasta]

Hi @amitchandak 

Not a problem.

Here you go.

October Total of 3 (3 Promoters)

November Total 5 (3 Detractors , 1 Passive 1 Promoter)

December Total 2 ( 1 Passive 1 Detractor)

Q4 Total 8 ( Detractor 3 , Passive 2 , Promoter 3)

They are also inside the simplified pbix from previous post linked here 

https://www.dropbox.com/s/vbm6pndnengdgox/NPS%20last%20score.pbix?dl=0 

They are in the PBIX file .

[amitchandak · Accepted solution]

I am also getting same result with a bit different formula.

Is there anything wrong, your looking to fixed.

https://www.dropbox.com/s/9246ahe8ge1p3lo/NPS%20last%20score%281%29.pbix?dl=0

[amitchandak]

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...