Excel Trend function in Power BI

[Dythern]

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!

[Dythern]

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!

[Anonymous]

HI @Dythern,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng