Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a requirment to calculate forecast by averaging the data for last 2years i.e if analytic is running in 2017 forecast should be calculated by averaging the data for 2016 and 2017 sales.
For the time period we have a drill down from Year-->Quarter-->Month . Has anybody came across this kind of problem , if so kindly suggest the solution
Is there a way in powerbi that we can rollup or rolldown the averages based on the dimension selected i.e Year or Quarter or Month
Hi @syedsiraj10,
The forecast is calculated by averaging the sales in Year 2016 and 2017, right? If so, could you please try this:
Forecast = CALCULATE ( SUM ( tablename[sales] ), FILTER ( tablename, YEAR ( tablename[date] ) = YEAR ( TODAY () ) || YEAR ( tablename[date] ) = YEAR ( TODAY () ) - 1 ) )
If I have something misunderstood, please share sample data and show us your desired output.
Regards,
Yuliana Gu
Hi @v-yulgu-msft,
Thanks for your reply. I am able to calulate forecast yearly , but the issue is when we drill down to quarters and months the average is appearing for all the values available in the chart.
My requirment is I need to average it for the first quater Q1 across years(2015,2016 and 2017 respectively), Similarly for second quarter I need to average Q2 across the 3 years and for other quarters as well.
And when we drill down to month level I need average of January month across years (2015,2016 and 2017) and for february month across years (2015,2016 and 2017) and average for other months as well.
Thanks,
Siraj
Hi @syedsiraj10,
Could you please share some sample data? And it would be better that you could post an image to show us your desired output.
Regards,
Yuliana Gu
User | Count |
---|---|
88 | |
74 | |
69 | |
65 | |
58 |
User | Count |
---|---|
104 | |
94 | |
76 | |
62 | |
59 |