Forum Discussion

syedsiraj10's avatar
syedsiraj10
Regular Visitor
8 years ago

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.    Fo...
v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

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

syedsiraj10's avatar
syedsiraj10
Regular Visitor
8 years ago

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