cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
syedsiraj10 Frequent Visitor
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
v-yulgu-msft Super Contributor
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.
syedsiraj10 Frequent Visitor
Frequent Visitor

Re: Calculate forecast for last 2 years data

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

v-yulgu-msft Super Contributor
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.

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 47 members 1,103 guests
Please welcome our newest community members: