Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |