NPS calculation : For date and till date

[avicric]

Hi all,
Need help to get For date and till date NPS score....

Example :

					For Date	Till Date
Date	Promoter	Detractor	Passive	total response	NPS score	NPS score
1​	10​	3​	1​	14​	50	50​
2​	20​	1​	5​	26​	73	65​
3​	5​	6​	0​	11​	-9	49​
total	35​	10​	6​	51​	49

 

[Misca]

Try something like:

Book1
	A	B	C	D	E	F	G
1	Date	Promoter	Detractor	Passive	total response	Daily NPS	Running NPS
2	1	10	3	1	14	50	50
3	2	20	1	5	26	73	65
4	3	5	6	0	11	-9	49
5	Total	35	10	6	51	49
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=SUM(B2:D2)
F2:F4	F2	=ROUND(([@Promoter]-[@Detractor])/SUM(NPSData[@[Promoter]:[Passive]])*100,0)
G2:G4	G2	=ROUND((SUM($B$2:$B2)-SUM($C$2:$C2))/SUM($B$2:$D2)*100,0)
B5	B5	=SUBTOTAL(109,[Promoter])
C5	C5	=SUBTOTAL(109,[Detractor])
D5	D5	=SUBTOTAL(109,[Passive])
E5	E5	=SUBTOTAL(109,[total response])
F5	F5	=ROUND((NPSData[[#Totals],[Promoter]]-NPSData[[#Totals],[Detractor]])/SUM(NPSData[[#Totals],[Promoter]:[Passive]])*100,0)

 

[avicric]

Try something like:

Book1
	A	B	C	D	E	F	G
1	Date	Promoter	Detractor	Passive	total response	Daily NPS	Running NPS
2	1	10	3	1	14	50	50
3	2	20	1	5	26	73	65
4	3	5	6	0	11	-9	49
5	Total	35	10	6	51	49
Sheet1

Cell Formulas
Range	Formula
E2:E4	E2	=SUM(B2:D2)
F2:F4	F2	=ROUND(([@Promoter]-[@Detractor])/SUM(NPSData[@[Promoter]:[Passive]])*100,0)
G2:G4	G2	=ROUND((SUM($B$2:$B2)-SUM($C$2:$C2))/SUM($B$2:$D2)*100,0)
B5	B5	=SUBTOTAL(109,[Promoter])
C5	C5	=SUBTOTAL(109,[Detractor])
D5	D5	=SUBTOTAL(109,[Passive])
E5	E5	=SUBTOTAL(109,[total response])
F5	F5	=ROUND((NPSData[[#Totals],[Promoter]]-NPSData[[#Totals],[Detractor]])/SUM(NPSData[[#Totals],[Promoter]:[Passive]])*100,0)

Thank you Misca...
But i was looking for a measure for these....

 

[Misca]

DAX measure? My bad.

DAX measures are a bit more tricky to write / understand without seeing the datamodel. I created a super simple model by unpivoting the Promoters, Detractors and Passives to a Role column and the values to a Score column. I also changed the Dates to actual dates. This way my Facts table ("Scores") had three columns and I created a date table which was connected to the Date column of my fact table.

Then I created a few helper measures and the actual NPS measures:

Total Responses:=sum(Scores[Score])
Promoters:=CALCULATE([Total Responses],Scores[Role]="Promoter")
Detractors:=CALCULATE([Total Responses],Scores[Role]="Detractor")

NPS:=ROUND(DIVIDE([Promoters]-[Detractors],[Total Responses])*100,0)

Running NPS :=
VAR DataEndDate =
    MAX ( Scores[Date] )
VAR EndDate =
    MIN ( MAX ( 'Calendar'[Date] ), DataEndDate )
VAR DateRange =
    FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] <= EndDate )
RETURN
    CALCULATE ( [NPS], DateRange )

Hide at least the Date and Score columns from the fact table and use the Date table to filter the dates.

 

[avicric]

DAX measure? My bad.

DAX measures are a bit more tricky to write / understand without seeing the datamodel. I created a super simple model by unpivoting the Promoters, Detractors and Passives to a Role column and the values to a Score column. I also changed the Dates to actual dates. This way my Facts table ("Scores") had three columns and I created a date table which was connected to the Date column of my fact table.

Then I created a few helper measures and the actual NPS measures:

Total Responses:=sum(Scores[Score])
Promoters:=CALCULATE([Total Responses],Scores[Role]="Promoter")
Detractors:=CALCULATE([Total Responses],Scores[Role]="Detractor")

NPS:=ROUND(DIVIDE([Promoters]-[Detractors],[Total Responses])*100,0)

Running NPS :=
VAR DataEndDate =
    MAX ( Scores[Date] )
VAR EndDate =
    MIN ( MAX ( 'Calendar'[Date] ), DataEndDate )
VAR DateRange =
    FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] <= EndDate )
RETURN
    CALCULATE ( [NPS], DateRange )

Hide at least the Date and Score columns from the fact table and use the Date table to filter the dates.

Awesome!!!!
Thanks you for taking the time....