Need a monthly Forecast of Rest of year using last year's data

[mixue100]

Hi all

I've data as per attached. Based on 2021's trend, I want a monthly forecast from July 22 - Dec 22. 

Say revenue for June 2021 was 100K and Revenue in July 2021 was 90K, that is a 10% drop. In the forecast I do this year, it will appear that I will also see a 10% drop from June 2022 (that is from 140K) . I cant figure out what to do on PBI to get that . Would be great if I can get some help from you experts. 

Month	Revenue	Profit
Jan-21	100,000	20,000	Actual
Feb-21	90000	15000	Actual
Mar-21	80000	12000	Actual
Apr-21	120000	22000	Actual
May-21	140000	25000	Actual
Jun-21	100,000	20,000	Actual
Jul-21	90000	15000	Actual
Aug-21	80000	12000	Actual
Sep-21	100,000	20,000	Actual
Oct-21	90000	15000	Actual
Nov-21	80000	12000	Actual
Dec-21	120000	22000	Actual
Jan-22	100,000	20,000	Actual
Feb-22	90000	15000	Actual
Mar-22	80000	12000	Actual
Apr-22	120000	22000	Actual
May-22	140000	25000	Actual
Jun-22	140000	25000	Actual
Jul-22	Need a forecast	Need a forecast	Forecast
Aug-22	Need a forecast	Need a forecast	Forecast
Sep-22	Need a forecast	Need a forecast	Forecast
Oct-22	Need a forecast	Need a forecast	Forecast
Nov-22	Need a forecast	Need a forecast	Forecast
Dec-22	Need a forecast	Need a forecast	Forecast

[v-stephen-msft]

Hi @mixue100 ,

Forecast is available for line charts only. If you want to apply the forecasting in the line chart, there's a forecast line built in the line chart which you can directly use. More details and limitations you can refer to Introducing new forecasting capabilities in Power View for Office 365 | Microsoft Power BI Blog | Mi....

If you want to add rows into your tables just like your example dispaly, you may try the solution below.

1.In general, your initial data will only have the following.

2.Then you need to extend your year and month column to the end of 2022.

Table 2 = DISTINCT(SELECTCOLUMNS(CALENDAR(DATE(2021,1,1),DATE(2022,12,31)),"Month",FORMAT([Date],"YYYY MMM")))

3.Create a relationship to join two tables.

4.Create a measure to calcualte the percentage current year compared to last year.

percentage =
VAR _current =
    MAX ( 'Table 2'[Month] )
VAR _previousmonth =
    CALCULATE ( SUM ( 'Table'[Profit] ), PREVIOUSMONTH ( 'Table 2'[Month] ) )
RETURN
    DIVIDE ( SUM ( 'Table'[Profit] ) - _previousmonth, _previousmonth )

5.Create a measure to calculate the previous year percentage.

percentage previous year =
VAR _current =
    MAX ( 'Table 2'[Month] )
RETURN
    IF (
        SUM ( 'Table'[Profit] ) = BLANK (),
        CALCULATE (
            [percentage],
            FILTER (
                ALL ( 'Table 2' ),
                YEAR ( [Month] )
                    = YEAR ( _current ) - 1
                    && MONTH ( [Month] ) = MONTH ( _current )
            )
        )
    )

6.Create a measure to get the result.

Actual and Foreacast Profit =
VAR _accumulate =
    SUMX (
        FILTER ( ALL ( 'Table 2' ), [Month] <= MAX ( 'Table 2'[Month] ) ),
        [percentage previous year]
    )
VAR _last =
    CALCULATE (
        LASTNONBLANK ( 'Table'[Profit], SUM ( 'Table'[Profit] ) ),
        ALL ( 'Table 2' )
    )
RETURN
    IF (
        SUM ( 'Table'[Profit] ) = BLANK (),
        CALCULATE (
            LASTNONBLANK ( 'Table'[Profit], SUM ( 'Table'[Profit] ) ),
            ALL ( 'Table 2' )
        ) * ( _accumulate + 1 ),
        SUM ( 'Table'[Profit] )
    )

Revenue is the same, and I will not repeat the steps.

You can download my attachment for details.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mixue100]

Hi

Thanks for coming back with a super detailed view. I still have issues when i apply the method onto my main data. 

the measures that i followed in step 4 and 5 are ok. 

However, the last measure for 'actual and forecast' actually reflected weird values in the future months (and that's what I need). 

I'm unable to do a 1-1 (both ways in my relationships between the table), because the raw data has multiple similar months in a particular column. I've hence set it this way: 

Is this the reason that its causing the future months to look weird? Any workarounds?