Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 rolli...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

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

  • Anonymous's avatar
    Anonymous
    3 years ago

    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])
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

 

 

 

Marcelo_Vieira's avatar
Marcelo_Vieira
Frequent Visitor
3 years ago

Anonymous 

 

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