Running net promoter score

Question

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

amitchandak · Answer

Ramendra , Try cumulative like this with date Calendar
Cumm positive = CALCULATE(SUM(Table[positive]),filter(date,date[date] &lt;=maxx(date,date[date])))
Cumm positive = CALCULATE(SUM(Table[positive]),filter(date,date[date] &lt;=max(Table[ Date])))

&nbsp;
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-bihttps://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

ramendra · Answer

&nbsp;Hi,Thankyou for your help amitchandak&nbsp;.&nbsp;Yes the calculate function is working fine if I want to see cumulative NPS for a month as per my requirement.&nbsp;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.&nbsp;Is there any way to add hours in date heirarchy like filter(date,date[hour] to get the result

amitchandak · Answer

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

ramendra · Answer

Hi Please find Sample input and expected output formatHappy&nbsp; numberTASK numberCreatedScore TypeVendorHAPP00001Task000101-04-20 0:00PositiveXHAPP00002Task000201-04-20 1:00PositiveYHAPP00003Task000301-04-20 2:00PositiveZHAPP00004Task000401-04-20 3:00PositiveZHAPP00005Task000501-04-20 4:00PositiveXHAPP00006Task000601-04-20 5:00NegativeYHAPP00007Task000701-04-20 6:00NegativeZHAPP00008Task000802-04-20 7:00NegativeZHAPP00009Task000902-04-20 8:00NegativeXHAPP00010Task001002-04-20 9:00NegativeYHAPP00011Task001102-04-20 10:00NegativeZHAPP00012Task001202-04-20 11:00PositiveZHAPP00013Task001303-04-20 12:00PositiveXHAPP00014Task001403-04-20 13:00PositiveYHAPP00015Task001503-04-20 14:00PositiveZHAPP00016Task001603-04-20 15:00PositiveZHAPP00017Task001704-04-20 23:00PositiveXHAPP00018Task001805-04-20 15:00PositiveYHAPP00019Task001906-04-20 15:00NeutralZHAPP00020Task002007-04-20 15:00NeutralZHAPP00021Task002108-04-20 15:00NeutralXHAPP00022Task002209-04-20 22:00NeutralYHAPP00023Task002310-04-20 15:00NeutralZHAPP00024Task002411-04-20 15:00NeutralZHAPP00025Task002512-04-20 15:00NeutralXHAPP00026Task002613-04-20 15:00NeutralYHAPP00027Task002714-04-20 15:00NeutralZHAPP00028Task002814-04-20 3:00NeutralZHAPP00029Task002914-04-20 8:00NeutralXHAPP00030Task003014-04-20 12:00PositiveYHAPP00031Task003114-04-20 15:00PositiveZHAPP00032Task003214-04-20 15:00PositiveZHAPP00033Task003314-04-20 15:00PositiveXHAPP00034Task003414-04-20 15:00PositiveYHAPP00035Task003514-04-20 15:00PositiveZ&nbsp;Sample outputMonthApr-20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Date03-Apr-20&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TimeNPS till the time for this month&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;0123456789101112131415161718192021222324xNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;yNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ZNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS ValueNPS Value&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-xicai · Answer

Hi Ramendra&nbsp;,
&nbsp;
Currently, it is not supported to add time to date hierarchy in Power BI.
&nbsp;
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.
&nbsp;
Column: Time = TIME(HOUR('Table'[Created]),MINUTE('Table'[Created]),SECOND('Table'[Created]))

Measure: NPS value = CALCULATE(SUM('Table'[Value]),FILTER(ALLSELECTED('Table'),'Table'[Created]&lt;=MAX('Table'[Created])))
&nbsp;
Finally, you can choose Matrix visual to display result, add Time column to Columns box of Matrix visual behind date field.

Best Regards,
Amy&nbsp;
&nbsp;
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.

Forum Discussion

Running net promoter score

7 Replies

Related content

net promoter score visual

Healthcare Net Promoter Score Dashboard

Calculating Score Difference between oldest and newest score

Running Maximum

Running sum