Calculate linear forecast based on last known value

[Anonymous]

Hi there,

I'm looking for a solution to calculate a linear forecast for 6 months based on the last known value. Example:

Date (DDMMYYY)	Measure
01-01-2021	100
02-01-2021	105
03-01-2021	103
....
06-07-2021	104
07-07-2021	102
Forecast	Expected Value
08-07-2021	102
...
07-01-2022	102

Thanks in advance.

[v-angzheng-msft]

Hi, @Anonymous 

It depends on the calculation method you want to use, and then create the DAX formula according to your calculation logic. You can also use a line chart to achieve the purpose of forecasting. There are forecast features in the line chart in the analysis pane.

See the links below for more information:

https://docs.microsoft.com/en-us/power-bi/transform-model/desktop-analytics-pane#apply-forecasting

https://community.powerbi.com/t5/Community-Blog/Predict-your-milestones-with-forecasting-in-Power-BI-Desktop/ba-p/81687

https://towardsdatascience.com/forecasting-in-power-bi-4cc0b3568443

https://www.youtube.com/watch?v=fLqvaWJtwhQ

https://www.youtube.com/watch?v=XIlPkyyztho

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-angzheng-msft ,

First of all thanks for your reply. I am aware of the forecasting features in the analysis pane, but I'd love to calculate such forecast with DAX so I could use the forecasting data for further calcualtions.

Anyhow, I realized I should've done some more research on what our end goal should be.

Thanks for the information nonetheless. I'll look into it.

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Use DAX to write formulas to forecast. I think this is not the difficulty of this problem. The key lies in the calculation logic to get the predicted data. This is more important.
For example: Linear Approximation, Linear Smoothing, least squares method, linear regression, etc.
This is the logic to get the data, so I think you need to find the logic algorithm for the prediction you want.

Here is an article about forecasting methods that may be helpful:

Understanding Forecast Levels and Methods 

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-angzheng-msft,

Thanks a lot for your help. On a first impression, I think the article is going to help a bunch. I'll be spending some time reading and understanding it. Thanks a lot!

Best regards,

Mattie

[smpa01]

@Anonymous  DAX does not have a built-in PREDICT or FORECAST algorithm. As long as you know the calculation of a Forecast you want to use, you can play it out with DAX.

[Anonymous]

@smpa01 Thanks for your reply.

I see, it seems like my lack of knowledge of DAX will be the problem then. Do you have any suggestions on how to calculate such forecast?

[amitchandak]

@Anonymous , usually forecast displayed in the line visual using analytics tab. But incase you need a new column or measure, you need to create a formula

[Anonymous]

@amitchandak Thanks for your reply.

Do you have any idea on how to calculate such forecast with DAX?