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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mixue100
Frequent Visitor

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

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. 

 

MonthRevenueProfit 
Jan-21          100,000            20,000Actual
Feb-219000015000Actual
Mar-218000012000Actual
Apr-2112000022000Actual
May-2114000025000Actual
Jun-21          100,000            20,000Actual
Jul-219000015000Actual
Aug-218000012000Actual
Sep-21          100,000            20,000Actual
Oct-219000015000Actual
Nov-218000012000Actual
Dec-2112000022000Actual
Jan-22          100,000            20,000Actual
Feb-229000015000Actual
Mar-228000012000Actual
Apr-2212000022000Actual
May-2214000025000Actual
Jun-2214000025000Actual
Jul-22Need a forecastNeed a forecastForecast
Aug-22Need a forecastNeed a forecastForecast
Sep-22Need a forecastNeed a forecastForecast
Oct-22Need a forecastNeed a forecastForecast
Nov-22Need a forecastNeed a forecastForecast
Dec-22Need a forecastNeed a forecastForecast

mixue100_0-1658234989181.png

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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....

vstephenmsft_0-1658457995014.png

 

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.

vstephenmsft_1-1658458397311.png

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")))

 

vstephenmsft_2-1658458481732.png

3.Create a relationship to join two tables.

vstephenmsft_3-1658458530823.png

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] )
    )

vstephenmsft_0-1658468988578.png

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.

 

Hi

 

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

 

mixue100_2-1658745547954.png

 

 

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: 

mixue100_1-1658745429651.png

 

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.