Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello!,
I need help, i'm using quick measure rolling average and it's calculating correctly but i want to use Year filter, i don't want it to calculate the rolling average taking the average from the prior year, i want it to start the measure for year, so when i calculate it, the result should be like this:
year | month | x measure | x rolling average |
2017 | Jan | 71% | 71% |
2017 | Feb | 64% | 68% |
2017 | Mar | 72% | 69% |
2017 | Apr | 72% | 70% |
2017 | May | 77% | 71% |
2017 | Jun | 75% | 72% |
2017 | Jul | 73% | 72% |
2017 | Aug | 76% | 73% |
2017 | Sep | 68% | 72% |
2017 | Oct | 66% | 72% |
2017 | Nov | 66% | 71% |
2017 | Dic | 66% | 71% |
2018 | Jan | 59% | 59% |
As you can see in 2018 the rolling average should start calculating without taking in consideration the accumulated of 2017, every year should be like this.
The quick measure dax formula is:
x rolling average =
IF(
ISFILTERED('NPS'[Fecha]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = ENDOFMONTH('NPS'[Fecha].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'NPS'[Fecha].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -COUNTA(NPS[Fecha]), MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('NPS'),
'NPS'[Fecha].[Year],
'NPS'[Fecha].[QuarterNo],
'NPS'[Fecha].[Quarter],
'NPS'[Fecha].[MonthNo],
'NPS'[Fecha].[Month]
),
__DATE_PERIOD
),
CALCULATE([x], ALL('NPS'[Fecha].[Day]))
)
)
i don't know exactly what should i change in the formula or if there's another way.
Thanks
Solved! Go to Solution.
Hi @avcr29
What happens if you try this
x rolling average = IF( ISFILTERED('NPS'[Fecha]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = ENDOFMONTH('NPS'[Fecha].[Date]) VAR __DATE_PERIOD = FILTER( DATESBETWEEN( 'NPS'[Fecha].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -COUNTA(NPS[Fecha]), MONTH)), __LAST_DATE ),YEAR(__LAST_DATE) = YEAR( 'NPS'[Fecha].[Date])) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('NPS'), 'NPS'[Fecha].[Year], 'NPS'[Fecha].[QuarterNo], 'NPS'[Fecha].[Quarter], 'NPS'[Fecha].[MonthNo], 'NPS'[Fecha].[Month] ), __DATE_PERIOD ), CALCULATE([x], ALL('NPS'[Fecha].[Day])) ) )
Hi @avcr29
What happens if you try this
x rolling average = IF( ISFILTERED('NPS'[Fecha]), ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."), VAR __LAST_DATE = ENDOFMONTH('NPS'[Fecha].[Date]) VAR __DATE_PERIOD = FILTER( DATESBETWEEN( 'NPS'[Fecha].[Date], STARTOFMONTH(DATEADD(__LAST_DATE, -COUNTA(NPS[Fecha]), MONTH)), __LAST_DATE ),YEAR(__LAST_DATE) = YEAR( 'NPS'[Fecha].[Date])) RETURN AVERAGEX( CALCULATETABLE( SUMMARIZE( VALUES('NPS'), 'NPS'[Fecha].[Year], 'NPS'[Fecha].[QuarterNo], 'NPS'[Fecha].[Quarter], 'NPS'[Fecha].[MonthNo], 'NPS'[Fecha].[Month] ), __DATE_PERIOD ), CALCULATE([x], ALL('NPS'[Fecha].[Day])) ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |