cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

2 ACCEPTED SOLUTIONS
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

@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])
7 REPLIES 7
Super User

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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?

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

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

Helper I

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

Helper I

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

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors