Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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...
Solved! Go to Solution.
@bdue Try FILTER(ALL('ServiceTracSurvey'), [CompleteDate] >= _StartDate && [date]<= _EndDate),
@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 Better Rolling Average - Microsoft Power BI Community
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 Try FILTER(ALL('ServiceTracSurvey'), [CompleteDate] >= _StartDate && [date]<= _EndDate),
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?
And when I try to use the completed date itself, it doesn't return any values...
@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.
@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!
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
User | Count |
---|---|
120 | |
69 | |
68 | |
57 | |
50 |
User | Count |
---|---|
166 | |
82 | |
68 | |
65 | |
54 |