Creating a forecasting plot that only returns values for the previous month and onward

[Anonymous]

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.

[v-yalanwu-msft]

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.

[v-yalanwu-msft]

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.

[Anonymous]

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.

[lbendlin]

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/1447523

[Anonymous]

Thank 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.