cancel
Showing results for
Did you mean:
Frequent Visitor

## Calculate forecast for last 2 years data

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

3 REPLIES 3
Super Contributor

## Re: Calculate forecast for last 2 years data

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Calculate forecast for last 2 years data

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

Super Contributor

## Re: Calculate forecast for last 2 years data

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 47 members 1,103 guests
Recent signins: