Forum Discussion

Dythern's avatar
Dythern
Regular Visitor
2 years ago

Excel Trend function in Power BI

Hello Community!

I am a beginner with Power BI and I want to ask for some help with the following calculation.

I am transferring my Excel formulas to Power BI and am stuck with the trend function. I have tried various solutions posted on this forum, but none of them returned the expected values.

I am using the trend function to predict next year's sales quantity per product based on the previous years quantities.
I also use some manual filtering for defining the previous years.

For example: The starting year for this analysis is 2017 (17), but I have products that were added to the portfolio in a later year. I don't want to influence the predicted quantity by adding years to the calculation where the product was not even in the supply.

In Power BI I have a date column with years, months, and days within and I have created an additional column where I have defined the start date for each product to make this easier. I call it "Function date".

The Excel version looks like this:



My problem is with Excel that I have to manually change a lot of functions each year.

I was looking at LINEST and LINESTX but I am lost. Also, I need the values returned in a table so I can export them.

I would appreciate any help! Thank you!

Need Help

2 Replies

  • Dythern's avatar
    Dythern
    Regular Visitor
    2 years ago

    Hello Xiaoxin Sheng,

     

    Thank you for addressing my problem!

     

    I have created a sample Excel file from the original without sensitive data. It contains all the relevant functions I use.

    I have also created a dummy data.xlsx and a dummy.pbix.

     

    All the average calculations are working as expected.

     

    I have tried two codes for trend calculation, but in both cases, the problem is, that it returns the sum of the total quantity. You can find these in the dummy.pbix.

     

    I have uploaded the files to my Google Dive: LINK 

     

    Thank you!