Forum Discussion
Not Sure What I'm doing Wrong
Can anyone spot what the issue is? I'm trying to calculate a trailing 12 month average. Here are the pieces involved. I will be more than happy to clarify anything because I've been stuck on this for weeks:
Count of survey results considered "positive":
Count of all survey results (positive, neutral, negative)
Percentage of positive feedback:
Attempt at calculating trailing 12 month average:
Results:
The results should definitely not be 100% for every month...
I've done something similar using different DAX (syntax may be a bit off)
You could put an offset column in your date table with inactive relationship:
Offset Month = DATE('Calendar'[SOM].[Year]-1,'Calendar'[SOM].[MonthNo],'Calendar'[SOM].[Day])
Then:
Trailing 12 Month Average = SUMX('All Survey Data',CALCULATE(SUM('All Survey Data'[Positive Feedback]),
FILTER('All Survey Data','All Survey Data'[Start Date]<=CALCULATE(MIN('Calendar'[Start Date]))&&'All Survey Data'[Start Date]>=MAX('Calendar'[Offset Month]))) / [All Groups]
4 Replies
Aron_MooreSolution Specialist
Could you put the other measures in that table as well to see if they're calculating correctly?
Aron_Moore Sure thing:
- Aron_Moore
I've done something similar using different DAX (syntax may be a bit off)
You could put an offset column in your date table with inactive relationship:
Offset Month = DATE('Calendar'[SOM].[Year]-1,'Calendar'[SOM].[MonthNo],'Calendar'[SOM].[Day])
Then:
Trailing 12 Month Average = SUMX('All Survey Data',CALCULATE(SUM('All Survey Data'[Positive Feedback]),
FILTER('All Survey Data','All Survey Data'[Start Date]<=CALCULATE(MIN('Calendar'[Start Date]))&&'All Survey Data'[Start Date]>=MAX('Calendar'[Offset Month]))) / [All Groups]