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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!:
The Definitive Guide to Power Query (M)

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

7 REPLIES 7
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!:
The Definitive Guide to Power Query (M)

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.