Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
bdue
Helper I
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:

bdue_0-1674768590704.png

Which generates this measure:

bdue_1-1674768614702.png

But, the resulting calculation for the rolling average returns the same value as the suveys that came in on that date.

bdue_2-1674768718295.png

What am I missing?  As I mentioned, this happens for counts and averages...

2 ACCEPTED SOLUTIONS

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

@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! 

 

bdue_0-1674832554022.png

 

 

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

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

@bdue Better Rolling Average - Microsoft Power BI Community



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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?

 

bdue_0-1674827037937.png

 

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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

 

bdue_1-1674829170981.png

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

bdue_2-1674829249061.png

 

 

bdue_0-1674829099964.png

 

@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_0-1674830490665.png

 

@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! 

 

bdue_0-1674832554022.png

 

 

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

@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

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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