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

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

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

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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

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

       

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

       

       

  • 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