How to forecast a straight-line average in DAX?

[ironryan77]

I developed a Power BI report where I need to chart the Net Payments per day for the current month (May 2017).  So I want to plot the actual data for the days that have transpired and I want to forecast the remaining days of this month according to a straight-line average. 

Numerator = CALCULATE(SUM(Intra_Month_Cash_Pacing[net_payment_amount])*[Inflate_Factor],ALLSELECTED(Intra_Month_Cash_Pacing),Intra_Month_Cash_Pacing[MM]=MONTH(TODAY()),Intra_Month_Cash_Pacing[YY]=2017)

Inflate_Factor = 1.47

Total Days in Month = CALCULATE(MAX(Intra_Month_Cash_Pacing[max_posting_day_of_MM]),ALL(Intra_Month_Cash_Pacing),Intra_Month_Cash_Pacing[MM]=MONTH(TODAY()),Intra_Month_Cash_Pacing[YY]=2017)

Slope = Numerator / [Total Days in Month]

I am using the built-in Line Chart visual to forecast.  But the problem is that this visual does not allow me to programmatically set the forecast length.  So I am hard-coding the length based on the number of days remaining--not good.  I want to plot the above straight-line slope for the days following the last day of actual data.  (The last day of data is 15 since this is plotting posting days and the total posting days in this month is 22).  Here is the formula I'm using to plot the actual / transpired amounts:

MTD 2017 Net-P = 
IF(ISBLANK(Intra_Month_Cash_Pacing[2017 MTD]),BLANK(), 
CALCULATE(
	'Intra_Month_Cash_Pacing'[2017 MTD],
	FILTER(
		ALLSELECTED('Intra_Month_Cash_Pacing'[Posting_Day_DD]),
		ISONORAFTER('Intra_Month_Cash_Pacing'[Posting_Day_DD], MAX('Intra_Month_Cash_Pacing'[Posting_Day_DD]), DESC)
	)
))

As you can see, this calculates all future values as Blanks, so that I can forecast these amounts instead.  But again, I need a dynamic way to select the forecast length.  How can I do this using DAX or another visual?

Here is how my data looks:

[GilbertQ]

Hi @ironryan77

Have you tried the Forecasting feature in Power BI?

I have used it in the past and it works really well. When using it you can select the seasonality, as well as the confidence factor, as well as how far forward you want to forecast.

https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-september-feature-summary/

Scroll down to the Forecasting under Analytics

[ironryan77]

Thanks, but as I said in my description, I need the forecast length to be dynamic, based on the number of remaining days in the month. 

[v-caliao-msft · Accepted solution]

@ironryan77,

Currently, we can only set the length (Points) when use forecasting feature. The lenght points cannot changed along with your data dynamically.

If you have any concern on this feature, you can submit your idea on the link below. If this feature was mentioned by mulitple users, product team will consider to add this feature to next release.
https://ideas.powerbi.com/forums/265200-power-bi-ideas

Regards,

Charlie Liao