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'm trying to get a fixed measure to show me a sum of rolling average measure at the end of the year ignoring all other date filters.
I have tried various options like below, but none seems to give me the desired result.
Run Rate test = CALCULATE([Run Rate (interim) rolling average];FILTER(ALL(_Calendar);_Calendar[Date].[Year] = "2020"))
Here is my rolling average measure.
Run Rate (interim) rolling average =
IF(
ISFILTERED('_Calendar'[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('_Calendar'[Date].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'_Calendar'[Date].[Date];
STARTOFMONTH(DATEADD(__LAST_DATE; -12; MONTH));
ENDOFMONTH(DATEADD(__LAST_DATE; 12; MONTH))
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('_Calendar');
'_Calendar'[Date].[Year];
'_Calendar'[Date].[QuarterNo];
'_Calendar'[Date].[Quarter];
'_Calendar'[Date].[MonthNo];
'_Calendar'[Date].[Month]
);
__DATE_PERIOD
);
CALCULATE(SUM('Revenue'[Run Rate (interim)]); ALL('_Calendar'[Date].[Day]))
)
)
Thanks in advance!
Tough to say without sample data to test. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Perhaps:
This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
I did not get it completely. But we have rolling like
Rolling 12 = CALCULATE(Average(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-12,MONTH))
or
YTD = CALCULATE(Average(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Hi @amitchandak ,
Those measures would be limited to YTD and would not roll forward, if necessary. They are not usable for forecasting. Im interested in the forecasting in particular.
I would like that Power BI displays me an END of year forecast no matter what filters Im applying in date slicer. I forecast all upcomming months by rolling avg, then make date slicers invalid with ALL() funcion and then apply year filter. Unforunately, Power BI is producing error, however formula looks logically correct to me. Any ideas where the mistake is?
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 |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |