NPS SCore.pbix (169.9 KB)
I hope I am able to make this easy to read. Sorry if I am not.
I am trying to create a Line Chart that shows the NPS( Feedback[NPS Score] ) score for the date range set by the Line Chart.
We Calculate our NPS & CSTAT scores by using the entries for the last 12 months. For example My October 2021 NPS and CSTAT score are the average of all entries November 1 2020 to October 31 2021.
I have tried the rolling average but I am not getting the same results that I do when I calculate them myself.
My raw data just gives me the 0-10 score no conversion, When I get it to Power BI, I insert a new column,"NPS"on the “Feedback” Table that gives the NPS a 100, 0, or -100.
I use a Date Table and have the Date the NPS Score was submitted related back to the date[date] field.
Can anyone point me in the right Direction?
I have read a few suggestions that say I need to add a column to with each months NPS. I am willing to do that but not sure how I can do that.
I was glad to see you using the Enterprise DNA Extended Date Table. The offsets columns that @Melissa has built into this table should help you make quick work of this problem.
If you still have questions after watching this, please just post a mockup of the results you are trying to achieve and I’ll be glad to work through the DAX to get you there.
I tried to follow along with the video but I am still not able to make it work correctly. I think my issue might be that My NPS scores are not a measure. I used Power Query to do the conversions from 10-0 to the 100, 0, -100 scores and put the score in its own column.
I used a quick Excel file to show what I am trying to accomplish. To explain that in more detail, My company calculates our NPS by getting the scores from the prior 12 months. So my October 2021 NPS score uses all entries from 11/1/2020 to 10/31/2021. As seen in the excel document that we have 84 entries for that time and I get an 87 for October.
What I would like to have on the report is the same as the chart on the “Running NPS Scores” worksheet in the excel file. I did not do December 2020 but the visual would show the last 12 months. It is fine with me if the Current month is not complete yet. Like the chart shows November but we have not yet finished the month so it will update as new ones come in this month.
I made a few clean-ups NPS SCore.pbix (174.8 KB)
to the original .pbix and has the same Data as the excel file. Excel is just more filtered so I could get to the example better.
Thank you again for all the assistance. This group has already gotten me so far in less than 3 months, I can not believe it. I just joined the classes a week or 2 ago but I have been watching the Youtube series for months. NPS Graph.xlsx (169.8 KB)
Please find hereby a solution to calculate the 12 months rolling CSAT- and NPS scores
See attached PBIX : NPS Score 12 mnt rolling II.pbix (177.0 KB)
In order to calculate the CSAT score as defined, the total CSAT-values needs to be divided by the number of entries for the rolling 12 months period, which can be calculated with three DAX formulas as stated below:
CSAT Total 12m rolling = CALCULATE(SUM(Feedback[CSAT]),
DATESBETWEEN(DateT[Date], Eomonth(max(DateT[Date]),-12)+1,LASTDATE(DateT[Date])))
Nr of entries 12m rolling = CALCULATE( COUNTA(Feedback[NPS Calculated]),
DATESBETWEEN(DateT[Date], Eomonth(max(DateT[Date]),-12)+1, LASTDATE(DateT[Date])))
CSAT score = DIVIDE( [CSAT Total 12m rolling], [Nr of entries 12m rolling], 0)
==>> The NPS score is determined likewise, based upon your “NPS calculated” out of the table “Feedback”.
`
PS:
As there was a difference between the SOLL Excel sheet and the Power BI calculations, I found 31 March for March and 30 July for July missing in your Excel sheet, I believe the Power BI numbers/table are correct.
== Table values calculated:
== Graph of CSAT and NPS scores in last 12 months:
Hello @mchandler, just following up if the response from the experts above help you solve your inquiry?
We’ve noticed that no response has been received from you since November 10. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
