Forum Discussion
Survey Data changes over time
Hi robofski ,
Based on your example data, I get the following output:
I suspect that in your real data the [Date Received] field isn't always the first of the month, right?
If so, then you can adjust the measure as follows:
_responsePercent = VAR __totalResponses = CALCULATE( COUNT(yourTable[Answer]), ALLEXCEPT(yourTable, yourTable[yourMonthField]) //Use month (or better, month/year) field instead of [Date Received] ) VAR __selectedResponses = COUNT(yourTable[Answer]) RETURN DIVIDE( __selectedResponses, __totalResponses , 0)As an aside: I notice that your output is only using months, not month/year. I would recommend updating the measure to using month/year in case you report over many years, in which case, the same months in different years wll be aggregated together.
Pete
Hi robofski ,
Based on your example data, I get the following output:
I suspect that in your real data the [Date Received] field isn't always the first of the month, right?
If so, then you can adjust the measure as follows:
_responsePercent =
VAR __totalResponses =
CALCULATE(
COUNT(yourTable[Answer]),
ALLEXCEPT(yourTable, yourTable[yourMonthField]) //Use month (or better, month/year) field instead of [Date Received]
)
VAR __selectedResponses =
COUNT(yourTable[Answer])
RETURN
DIVIDE( __selectedResponses, __totalResponses , 0)
As an aside: I notice that your output is only using months, not month/year. I would recommend updating the measure to using month/year in case you report over many years, in which case, the same months in different years wll be aggregated together.
Pete
You Sir are a legend! Thanks a lot, you were bang on with my data being all over the month so creating a YearMonth column and using that gets me exactly what I needed.
Thanks again!