Running net promoter score

[Ramendra]

Hi team,
I want to make a graph where I want see the nps progress from day 1 to current day for a current month.
I don't want nps per day but I want running nps.
For example if first day positive, negative and neutral scores are 10, 2, 1 and second day 10, 1, 1 then then total nps on second day would be calculated by 20 positive, 3 negative, 2 neutral.
So I want to see this graph of nps as running nps.

I also want to see the status of nps at each 4 hours in a day

[v-xicai]

Hi @Ramendra ,

Currently, it is not supported to add time to date hierarchy in Power BI.

As a workaround, you may use series of TIME functions to get Time column, format the data type of Time column as "hh:mm:ss" or other format, and create measure like DAX below to get cumulative NPS value.

Column: Time = TIME(HOUR('Table'[Created]),MINUTE('Table'[Created]),SECOND('Table'[Created]))

Measure: NPS value = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Created]<=MAX('Table'[Created])))

Finally, you can choose Matrix visual to display result, add Time column to Columns box of Matrix visual behind date field.

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ramendra]

Hi But your screenshots showing the data for feb and March also. But I have given data only for april.

I want to see MTD for april at a perticular time for example I want MTD NPS on 3rd april till 3 PM.

Can you please help.

[v-xicai]

Hi @Ramendra ,

Did you test using the formulas above?

Best Regards,

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Ramendra , Try cumulative like this with date Calendar

Cumm positive = CALCULATE(SUM(Table[positive]),filter(date,date[date] <=maxx(date,date[date])))
Cumm positive = CALCULATE(SUM(Table[positive]),filter(date,date[date] <=max(Table[ Date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Ramendra]

 Hi,

Thankyou for your help @amitchandak .
 Yes the calculate function is working fine if I want to see cumulative NPS for a month as per my requirement.

But I am not able to identify what to do to see the next level of same hierarchy which is like , I want to see the cumulative NPS for this month in three cotegories like morning status , afternoon status and then evening status.

Is there any way to add hours in date heirarchy like filter(date,date[hour] to get the result

[amitchandak]

@Ramendra , Can you share sample data and sample output. I will try

[Ramendra]

Hi Please find Sample input and expected output format

Happy  number	TASK number	Created	Score Type	Vendor
HAPP00001	Task0001	01-04-20 0:00	Positive	X
HAPP00002	Task0002	01-04-20 1:00	Positive	Y
HAPP00003	Task0003	01-04-20 2:00	Positive	Z
HAPP00004	Task0004	01-04-20 3:00	Positive	Z
HAPP00005	Task0005	01-04-20 4:00	Positive	X
HAPP00006	Task0006	01-04-20 5:00	Negative	Y
HAPP00007	Task0007	01-04-20 6:00	Negative	Z
HAPP00008	Task0008	02-04-20 7:00	Negative	Z
HAPP00009	Task0009	02-04-20 8:00	Negative	X
HAPP00010	Task0010	02-04-20 9:00	Negative	Y
HAPP00011	Task0011	02-04-20 10:00	Negative	Z
HAPP00012	Task0012	02-04-20 11:00	Positive	Z
HAPP00013	Task0013	03-04-20 12:00	Positive	X
HAPP00014	Task0014	03-04-20 13:00	Positive	Y
HAPP00015	Task0015	03-04-20 14:00	Positive	Z
HAPP00016	Task0016	03-04-20 15:00	Positive	Z
HAPP00017	Task0017	04-04-20 23:00	Positive	X
HAPP00018	Task0018	05-04-20 15:00	Positive	Y
HAPP00019	Task0019	06-04-20 15:00	Neutral	Z
HAPP00020	Task0020	07-04-20 15:00	Neutral	Z
HAPP00021	Task0021	08-04-20 15:00	Neutral	X
HAPP00022	Task0022	09-04-20 22:00	Neutral	Y
HAPP00023	Task0023	10-04-20 15:00	Neutral	Z
HAPP00024	Task0024	11-04-20 15:00	Neutral	Z
HAPP00025	Task0025	12-04-20 15:00	Neutral	X
HAPP00026	Task0026	13-04-20 15:00	Neutral	Y
HAPP00027	Task0027	14-04-20 15:00	Neutral	Z
HAPP00028	Task0028	14-04-20 3:00	Neutral	Z
HAPP00029	Task0029	14-04-20 8:00	Neutral	X
HAPP00030	Task0030	14-04-20 12:00	Positive	Y
HAPP00031	Task0031	14-04-20 15:00	Positive	Z
HAPP00032	Task0032	14-04-20 15:00	Positive	Z
HAPP00033	Task0033	14-04-20 15:00	Positive	X
HAPP00034	Task0034	14-04-20 15:00	Positive	Y
HAPP00035	Task0035	14-04-20 15:00	Positive	Z

Sample output

Month

Apr-20

Date

03-Apr-20

Time

NPS till the time for this month

0

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

x

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

y

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

Z

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value

NPS Value