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.
Hey,
I tried to go for the Moving Average building with PowerBI.
The problem is that the moving average is calculated for the future, which does not make any sense.
I wanted to do a six-month-rolling average with the quick measure (problem of above appeared), then with the formula = calculate(average([Sales]), DatesInPeriod([date];Lastdate([date]), -6, MONTH). Same Problem, again. So I do not know what is the problem. Perhaps somebody could offer me a simple sample PBIX data with a 6 month moving average over 2 years data..
Solved! Go to Solution.
Hi @Rotergnom2,
I would suggest you create a date table in your scenario. Please check out the attached demo and the measure below.
Measure = VAR maxFactDate = CALCULATE ( MAX ( FactTable[Date] ), ALL ( 'Calendar' ) ) RETURN IF ( MAX ( 'Calendar'[Date] ) > maxFactDate, BLANK (), CALCULATE ( AVERAGEX ( SUMMARIZE ( 'FactTable', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month], "MonthTotal", SUM ( FactTable[Sales] ) ), [MonthTotal] ), DATESINPERIOD ( 'Calendar'[date], LASTDATE ( 'Calendar'[date] ), -6, MONTH ) ) )
Best Regards,
Dale
Hi @Rotergnom2,
Could you please mark the proper answer as a solution?
Best Regards,
Dale
Hi @Rotergnom2,
I would suggest you create a date table in your scenario. Please check out the attached demo and the measure below.
Measure = VAR maxFactDate = CALCULATE ( MAX ( FactTable[Date] ), ALL ( 'Calendar' ) ) RETURN IF ( MAX ( 'Calendar'[Date] ) > maxFactDate, BLANK (), CALCULATE ( AVERAGEX ( SUMMARIZE ( 'FactTable', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month], "MonthTotal", SUM ( FactTable[Sales] ) ), [MonthTotal] ), DATESINPERIOD ( 'Calendar'[date], LASTDATE ( 'Calendar'[date] ), -6, MONTH ) ) )
Best Regards,
Dale
So, is your calculation correct other than the fact that it includes future estimations?
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |