YTD rolling average is returning the same values as current period

[bdue]

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...

[Greg_Deckler · Accepted solution]

@bdue Try FILTER(ALL('ServiceTracSurvey'), [CompleteDate] >= _StartDate && [date]<= _EndDate),

[Greg_Deckler]

@bdue Better Rolling Average - Microsoft Power BI Community

[bdue]

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?

[Greg_Deckler · Accepted solution]

@bdue Try FILTER(ALL('ServiceTracSurvey'), [CompleteDate] >= _StartDate && [date]<= _EndDate),

[bdue]

Nice, that resoved the errors, but I am only getting a value for January?

And when I try to use the completed date itself, it doesn't return any values...

[bdue]

@Greg_Deckler Okay, I had an obvious error in not updating -3 months from your video to -12, so corrected that.  I also removed the [month] from the date portions and now I am getting results, but they don't make sense... The rolling values fluctuate too much to be accurate.

eNPSRolAvg =

VAR _EndDate = Max('ServiceTracSurvey'[CompletedDate])

VAR _12MonthsAgo = EOMONTH(_EndDate, -12)

VAR _StartDate = Date(Year(_12MonthsAgo), month(_12MonthsAgo),1)

VAR _Table =

    Summarize(

        Filter(All('ServiceTracSurvey'),[CompletedDate] >= _StartDate && [CompletedDate] <= _EndDate),

        'ServiceTracSurvey'[completedDate],

        "_value",ServiceTracSurvey[eNPS])

Return

    AverageX(_table,ServiceTracSurvey[eNPS])

[bdue · Accepted solution]

@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])

Return

    AverageX(_table,ServiceTracSurvey[eNPS])

[Marcelo_Vieira]

@bdue 

Does this help?? maybe you have to adequate the tables and columns names and on the "countrows" change to use a SUM of your desired column.

average per day same year =
VAR max_selected_date =
    MAX ( 'servicetracsurvey'[completedDate] )
VAR max_year =
    YEAR ( MAX ( 'servicetracsurvey'[completedDate] ) )
VAR table_to_average =
    CALCULATETABLE (
        VALUES ( 'servicetracsurvey'[completedDate] ),
        YEAR ( 'servicetracsurvey'[completedDate] ) = max_year,
        'servicetracsurvey'[completedDate] <= max_selected_date,
        ALLSELECTED ()
    )
VAR result =
    AVERAGEX ( table_to_average, CALCULATE ( COUNTROWS ( 'servicetracsurvey' ) ) )
RETURN
    result