Forum Discussion
Creating a forecasting plot that only returns values for the previous month and onward
Hi All,
I've created a lineplot with multiple series and one of the series is a forecasting line. For this line I'd like it to begin in the previous month only and continue for all the future forecasts, rather than spanning the entirety of the year. Below is my current progress and code with explanations.
Plot:
The red circled portion is what I want removed, though I still want the cumulative data to account for its presence
Code:
BudgetDate[Date] is a column of the dates in the format of 00/00/0000. The table as a whole converts SQL data into a monthly series in the format:
tblForcast[Value] is a column of my forcasting numbers which are in the form of dollars ie. $1,000,000
tblForecast[dtForecast] is a column of the dates of my forecasting values
Currently the code runs perfectly and calculates the cumulative forecasting data. If I insert something like
"&& 'tblForecast'[dtForecast]>= TODAY()" The line still functions but since the cumulative data is missing 3 values the entire line drops down. Example of what happens below
Any assistance is HIGHLY appreciated!
Bonus: In a similar fashion I'd like the blue line to end where the forecasting red line begins. So it should end in the previous month constantly.
5 Replies
Hi, Anonymous ;
Try it.
Measure = IF ( EOMONTH ( MAX ( [dtForecast] ), 0 ) < EOMONTH ( TODAY (), 0 ), BLANK (), IF ( EOMONTH ( MAX ( [dtForecast] ), 0 ) = EOMONTH ( TODAY (), 0 ), CALCULATE ( SUM ( [Value] ), FILTER ( ALL ( tblForcast ), [dtForecast] <= EOMONTH ( TODAY (), 0 ) )), SUM ( [Value] )))If not right, can you provide more information, I can't fully understand or reproduce it.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.This was your code which I implemented, which got me 90% of the way to my goal. Unfortunetly below is the result (Purple Line).
The plot was perfect up until the month of JUL where it stopped being cumulative for some reason. I circumvented this by having to add an entirely new view in a SQL database which has the data already cumulative, which is unfortunate because I originally set out to do this with my current tables.
Hi, Anonymous ;
Sorry, I don't know your data, so I don't know the reason. Can you share a simple file after removing sensitive information?
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. Avoid posting screenshots of your source data if possible.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Thank you for the response, below are samples of my data tables!
BudgetDate:
Date Month 01/01/2022 JAN 03/03/2022 MAR 04/11/2022 APR 06/01/2022 JUN tblForcast
dtForecast Value Saturday, January 1, 2022 $971,100 Tuesday, March 3, 2022 $125,000 Monday, Aprill 11, 2022 $1,000,000 Wednesday, June 1, 2022 $4,600,000 Desired outcome: Redline only shows data which is past the previous month, and *bonus* light blue line only shows data before the current month. The issue is the red line needs to still have the cumulative data from previous months included as the line is cumulative. Using my sample data, the first point would only appear for April but display a value of $2.1 M or $2,096,000.
