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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
readyOH
Frequent Visitor

How to do a Forecast?

Hi,

 

I know that the forecast functionnality has been removed from PowerBI https://support.powerbi.com/forums/265200-power-bi/suggestions/8790061-add-forecasting-feature

 

but do you have a formula (dax) for it? For example it generates an average of the previous months and generates a trend for the next 3 months.

 

Thanks

1 ACCEPTED SOLUTION

Try taking a look at the following links. Hope this helps.

 

Issue One:http://www.daxpatterns.com/cumulative-total/

 

Issue Two: http://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/

Business Intelligence Architect / Consultant

View solution in original post

16 REPLIES 16
2000daytona955i
Regular Visitor

I took the approach of using DAX to grab historical points in a time-series to plot future values (via measures). The trick was in creating a date table that had future dates to serve as the parent to the data table. Although this is a rather simple approach, I was able to get a pretty accurate fit for web traffic that still allowed PowerBI cross-viz drill-functionality and PowerBI service support (wheras R does not). I have the pbix file and the supporting data sets in the zip at the bottom of the post.

 

http://fountainanalytics.com/creating-simple-time-series-forecasting-using-microsoft-powerbi-and-dax...

 

Regards,

  David

Hi David the link doesnt have any attachment. Please help in getting those. I am so interested in viewing the post.

Sorry, I noticed my site was down and have corrected this. You should now be able to download the .zip file in the bottom of the article.  Here is the direct link, if that is easier : http://fountainanalytics.com/wp-content/uploads/2016/09/TimeSeriesForecast.zip

 

Regards,

 D

Sorry for the bit of Thread necromancy but I'm having an issue.  I used David's solution to attempt to do a forecast and I can't understand the behavior that is occuring with the visual.

 

I have a count and a Trend count, I also have some filters on the side.

ss1.jpg

 

After I click on any filter the Values disappear for the Trend Line when using the line and stacked column chart.

 

ss2.jpg

I used “see data” on the first visual, and the Data is there for Claims (trend 2)

ss3.png

I did this on the 2nd Visualization as well, where the Trend Line vanishes. But when I look at the See data there Is data for those dates. Any idea what’s going on?

ss4.png

The one thing you will have to do is have a separate table with the dates - which is a parent to the table with the actuals and the forecast DAX. Otherwise, the DAX formula only looks at previous dates. If you look at the example file in the article, you will see the date table and the joins. Hope this helps.

 

http://fountainanalytics.com/wp-content/uploads/2016/09/TimeSeriesForecast.zip

 

D

David,

Do you think there could be an issue with your prior day formula? It's referring to the site_data[date], -1 but the others like prior week are looking at the CSV_datelookup[date].

 

Pageviews (Prior Day) =
SUMX(
CALCULATETABLE(Site_Data,
DATEADD(ALL(Site_Data[Date]),-1,DAY),
ALL(Site_Data[Date])),
Site_Data[Pageviews])

 

Pageviews (Prior Week) =
SUMX(
CALCULATETABLE(Site_Data,
DATEADD(CSV_Datelookup[Date],-7,DAY),
ALL(Site_Data[Date])),
Site_Data[Pageviews])

Greg_Deckler
Super User
Super User

See my linear regression post:

http://community.powerbi.com/t5/Desktop/Simple-Linear-Regression-with-DAX/m-p/9272#M1736

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

What you want to do sounds very much like simple linear regression. You should be able to do that in a DAX measure or combination of measures/columns. Can you give me some sample data?

 

Here is Linear Regression

https://www.easycalculation.com/statistics/learn-regression.php

 

Here is a DAX pattern for moving average that you might find useful

http://www.daxpatterns.com/statistical-patterns/

 

 

 

 

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

thank you @Greg_Deckler  I am not familiar with linear regression. I have have this sample data

 

MonthSales
January375,09
February375,01
March375
April374,95
May374,93
June374,92
July374,88
August374,8
September374,71
October ?
November ?
December ?

 

What I want to do : a formula which calculates automatically the sales for the next three months, based on an average of the previous month. if we are in september, it generates a trend for oct/nov/dec based on the average. If we are in december, then jan/feb/march.

 

Can you help me?

 

 

 

Assuming that "," are "." in my geographic context, I plugged your numbers into my model and came up with:

 

Data:

X,Y
1,375.09
2,375.01
3,375.00
4,374.95
5,374.93
6,374.92
7,374.88
8,374.80
9,374.71

 

Estimates (from linear regression)

10,374.72

11,374.68

12,374.64

 

That's linear regression, if you want a rolling average, then you could use the pattern here:

http://www.daxpatterns.com/statistical-patterns/

 

 

For rolling average. However, I cannot tell from your information how many months that you want to include in your rolling average, the last 3 months? So September would take into account July, August and September to predict December, June, July and August to predict November and May, June, July to predict October, or do you want all available dates to be used in prediction such that [Start] through July to predict October, [Start] through August to predict November and [Start] through September to predict December? Or something else all together??


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...


@smoupre wrote:

For rolling average. However, I cannot tell from your information how many months that you want to include in your rolling average, the last 3 months? So September would take into account July, August and September to predict December, June, July and August to predict November and May, June, July to predict October, or do you want all available dates to be used in prediction such that [Start] through July to predict October, [Start] through August to predict November and [Start] through September to predict December? Or something else all together??


 

thank you.

Not based on the previous 3 months but just the previous months. in dax formula, there is "filter"

FILTER(ALL('Table1'[Month]);'Table1'[Month]<= MAX('Table1'[Month])) is it what I have to use?

 

So, if all you want is an average and it doesn't need to be rolling, just create a measure like:

 

Average = AVERAGE([Sales])

 

Put it on your canvas and you have your estimate. Am I missing something?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

An average could be great but :

let say we are in october, i already have the sales for january to september. of course i have no data for next month october, november, december

 

but i want to make a chart or table with the sales of the year 2015 (Y) and month (X) january to december.

How i can make it because i have no row data for october, november, december? 

@readyOH Try looking at the following video. It might help provide some context to the solution that @Greg_Deckler Provided .

 

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

Business Intelligence Architect / Consultant

@PowerBIGuy @Greg_Deckler

thank you guys

 

The first issue : the value

 

yep linear regression would be great (especially for seasonality), but I need something more "basic" and easy to do.

Average fits my need. That's what we already use in excel.

So we generate an average monthly sales of previous months and this value will be the same for the next months -for each months (I know that is not perfect way to forecast).

so the average sales will be? :

CALCULATE(AVERAGE('Table1'[Sales]);FILTER(ALL('Table1'[Month]);'Table1'[Month]<= MAX('Table1'[Month]))

 

The problem is that Filter in the formula is cumulative http://community.powerbi.com/t5/Desktop/DAX-Filter-issue/m-p/9368

and

CALCULATE(AVERAGE('Table1'[Sales]);'Table1'[Month]<= MAX('Table1'[Month])) generates an error. Using MAX is not possible in this formula.

 

 

The second issue : is the way I will show the value average montly sales for the next monts in a chart

The average is a measure. 

And I do not have in my table set : october november, december

So how can i make a charts like this one.

forecast charts.jpg

 

 

 

 

 

 

 

 

 

 

 

Sorry if it is a stupid question, but for my previous sales, of course i have a row for each day of each months, but for next months i have nothing. In the video, it is easy for the who uses excel, he can add the rows of the next year and he has the value thanks to his formula.

 

Do you have a solution?

Try taking a look at the following links. Hope this helps.

 

Issue One:http://www.daxpatterns.com/cumulative-total/

 

Issue Two: http://blog.gbrueckl.at/2015/04/recursive-calculations-powerpivot-dax/

Business Intelligence Architect / Consultant

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.