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])
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Can you expand on the Filterall? Where should it go?

I have the same issue. The difference is the value I am averaging is a measure. I do have a date table that conrols it.

 

Thx