Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
syedsiraj10
Regular 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
Employee
Employee

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.

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.