Forum Discussion
YTD rolling average is returning the same values as current period
Hi, I am working on a PowerBI measure to calculate the rolling 12 month average of employee surveys, but each time I try the "quick measure" tools to generate YTD counts, running totals or this rolling average, the calulated result is the same as what I have for each day.
Here is what I am using:
Which generates this measure:
But, the resulting calculation for the rolling average returns the same value as the suveys that came in on that date.
What am I missing? As I mentioned, this happens for counts and averages...
Anonymous Try FILTER(ALL('ServiceTracSurvey'), [CompleteDate] >= _StartDate && [date]<= _EndDate),
Greg_Deckler Marcelo_Vieira Thank you both for the tips, I love this community. I used Greg's general layout and created a new Year Month measure based on my completed date so it organizes the chart better, and it appears to be working!
YearMonth = Format(ServiceTracSurvey[CompletedDate], "yyyy-mm")eNPSRollAvg =VAR _EndDate = Max('ServiceTracSurvey'[YearMonth])VAR _12MonthsAgo = EOMONTH(_EndDate, -12)VAR _StartDate = Date(Year(_12MonthsAgo), month(_12MonthsAgo),1)VAR _Table =Summarize(Filter(All('ServiceTracSurvey'),[YearMonth] >= _StartDate && [YearMonth] <= _EndDate),'ServiceTracSurvey'[YearMonth],"_value",ServiceTracSurvey[eNPS])ReturnAverageX(_table,ServiceTracSurvey[eNPS])
8 Replies
Thank you for the quick response and video Greg_Deckler . Apologies for what is probably an obvious thing, but I am not very familiar with Dax and in the summarize function, the [Date] and 'Table'[Month] portions are giving me errors. All I can find is measures, do I need another "all" command before [date] so that I can reference the table rather than just measures?
Anonymous Try FILTER(ALL('ServiceTracSurvey'), [CompleteDate] >= _StartDate && [date]<= _EndDate),