I am trying to make a report to calculate net promoter score based off of a survey sent to customers.
My data has:
a unique number for each response
details about the person who responded
the date the response was given
the score the person gave out of 10.
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
@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