Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to make a report to calculate net promoter score based off of a survey sent to customers.
My data has:
The responses are categorized into promoters (score >= 9) and detractors (score <= 6) and then the net promoter score is calculated as (# of promomoters) - (# of detractors) / (# of responses)
The end result is that I want to have a bar graph for each month which displays a rolling calculation of the net promoter score based on the responses from the last 12 months. So the bar for June 2020 would calculate the score based on responses from June 2019 through June 2020, etc. I tried doing this with calculated columns, but the problem is that I need to then be able to filter and slice this graph based on different values both in the table of raw data and in related tables. For reference I had tried this solution but it wasn't able to be sliced: https://community.powerbi.com/t5/Desktop/Trailing-12-or-Rolling-12-month-sum/m-p/164419#M71715
Solved! Go to Solution.
Solved my problem using unpivoting. Made columns for each of the 12 months following the submission date and unpivoted it, then used the date as my x axis and measures as the y axis
Solved my problem using unpivoting. Made columns for each of the 12 months following the submission date and unpivoted it, then used the date as my x axis and measures as the y axis
I hope that this reached the person who posted this. I am tryiojg to accomplish the same as you are for NPS so that I can get a line graph that has the 12 month NPS score for each month.
January = February 1 2020 to January 2021 NPS Average
February = March 2020 to February 28 2021NPS Average
and so on.
I reviewd your answer hefre but I was not able to follow what you suggested if you could, would you go into a little more detail?
Hi,
Share some data and show the expected result.
@Anonymous , You can get rolling like
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-12,MONTH))
with date table.
If you want display for 12 months -https://www.youtube.com/watch?v=duMSovyosXE
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/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
@amitchandak I have tried those formulas and while they did work, the problem was that when I would slice the data, those values would not change because they were in a calculated column. I tried using a measure but I could only get the measure to calculate for the current month, not the past 12
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |