Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Solved! Go to Solution.
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
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |