Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Guys,
I have created a 12 months moving average by using quick measure but my line is showing future months data also.
For example I have date till March 31st but the 12 months graph is showing till Year end.
Regards
Drake
Solved! Go to Solution.
Hi @Anonymous ,
You could modify the quick measure like below.
value rolling average 2 = IF ( ISFILTERED ( 'Sheet25'[Date] ), 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 ( 'Sheet25'[Date].[Date] ) VAR __DATE_PERIOD = DATESBETWEEN ( 'Sheet25'[Date].[Date], STARTOFMONTH ( DATEADD ( __LAST_DATE, -12, MONTH ) ), ENDOFMONTH ( DATEADD ( __LAST_DATE, 1, MONTH ) ) ) VAR average_ = AVERAGEX ( CALCULATETABLE ( SUMMARIZE ( VALUES ( 'Sheet25' ), 'Sheet25'[Date].[Year], 'Sheet25'[Date].[QuarterNo], 'Sheet25'[Date].[Quarter], 'Sheet25'[Date].[MonthNo], 'Sheet25'[Date].[Month] ), __DATE_PERIOD ), CALCULATE ( SUM ( 'Sheet25'[value] ), ALL ( 'Sheet25'[Date].[Day] ) ) ) RETURN IF ( MAX ( 'Sheet25'[Date] ) = BLANK (), BLANK (), average_ ) )
Then you could get your desired output.
Best Regards,
Cherry
Hi @Anonymous ,
By my test, I could reproduce your issue when I create quick measure like below.
This issue is casued by that we set the Periods before to be 12 so it will calculate till year end.
For your requirement, you could set the parameter of Periods before to be 0 so that it will calculate the rolling average till the last date in your data sample.
Best Regards,
Cherry
Hi
I dont think we can keep 0 there, I need to calculate 12 months rolling avg and If i keep o then measure is not working.
Regards
Drake
Hi @Anonymous ,
You could modify the quick measure like below.
value rolling average 2 = IF ( ISFILTERED ( 'Sheet25'[Date] ), 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 ( 'Sheet25'[Date].[Date] ) VAR __DATE_PERIOD = DATESBETWEEN ( 'Sheet25'[Date].[Date], STARTOFMONTH ( DATEADD ( __LAST_DATE, -12, MONTH ) ), ENDOFMONTH ( DATEADD ( __LAST_DATE, 1, MONTH ) ) ) VAR average_ = AVERAGEX ( CALCULATETABLE ( SUMMARIZE ( VALUES ( 'Sheet25' ), 'Sheet25'[Date].[Year], 'Sheet25'[Date].[QuarterNo], 'Sheet25'[Date].[Quarter], 'Sheet25'[Date].[MonthNo], 'Sheet25'[Date].[Month] ), __DATE_PERIOD ), CALCULATE ( SUM ( 'Sheet25'[value] ), ALL ( 'Sheet25'[Date].[Day] ) ) ) RETURN IF ( MAX ( 'Sheet25'[Date] ) = BLANK (), BLANK (), average_ ) )
Then you could get your desired output.
Best Regards,
Cherry
If statement works fine.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |