Combined Actual and Forecast as cumulative line chart

[chrismcdonald]

Looking for some help please. Everytime I do this I either can only get the forecast data to display or when I get the forecast and actual data it counts up then drops down.

I am trying to get a graph to show a cumulative forecast and actual line. as per image 3 below.

Current forumulas I am using are 

Combined Cumulative measure = IF([Cumulative Actual Line AAA]=BLANK(),[Cumulative Future Forecast]+[Cumulative Actual Line AAA],[Cumulative Actual Line AAA])

Cumulative Actual Line AAA =

IF (

MIN ( 'Calendar'[Date] )

>= TODAY(), BLANK(),

CALCULATE (

DISTINCTCOUNT('Access Implementation'[PCL No.]), FILTER('Access Implementation','Access Implementation'[AAA ACHIEVED (Actual) - Text] <> ""),

FILTER (

ALLSELECTED( 'Calendar'),'Calendar'[Date] <= MAX ( 'Calendar'[Date])

),

USERELATIONSHIP ( 'Calendar'[Date], 'Access Implementation'[AAA ACHIEVED (Actual)] )

))

Cumulative Future Forecast =

VAR LastActualDate =

CALCULATE(

MAX('Access Implementation'[AAA ACHIEVED (Actual)]),

REMOVEFILTERS()

)

VAR Actuals = [09.1 - COUNT AAA ACHIEVED - ACTUAL (USERELATIONSHIP)]

VAR FutureForecast =

CALCULATE([Cumulative Forecast Line AAA],KEEPFILTERS('Calendar'[Date]>LastActualDate)

)

VAR Result = FutureForecast + Actuals

RETURN

FutureForecast

Image 1 - Expected data (Expexted Results column added manually via Excel for demonstration purposes) 

Image 2 - What the graph currently looks like

Image 3 - What the graph should so. Acuals at the start and then the forecasts continue preferably in a different colour.

Any assistance would be much appreciated

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-angzheng-msft · Accepted solution]

Hi, @chrismcdonald 

Based on what @Ashish_Mathur  has done, to create another measure like this:

_IF = IF(ISBLANK([Actuals]),BLANK(),IF(
    [Actuals]<>[Measure],[Measure],[Actuals]))

Or

_IF2 = IF(ISBLANK([Actuals]),BLANK(),IF(
    [Actuals]<>[Measure],BLANK(),[Actuals]))

Result:

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[chrismcdonald]

Thank you.. This has been very helpful and used it in combination of what Ashish had provided.

Thanks again to you both

[v-angzheng-msft]

Hi, @chrismcdonald 

I'm glad to hear that this was helpful to you, and it would be great if you would consider marking my post as an Answer, it would mean a lot to me, and it will help the others in the community find the solution easily if they face the same problem with you.😀

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Share the link from where i can download your MS Excel workbook.

[chrismcdonald]

Hi Ashish,

Link is https://drive.google.com/file/d/1xclHWhwf4vHJ3h9yq7_B3FVb5n-jR2gI/view?usp=sharing

Hopefully that link works.

[Ashish_Mathur]

Hi,

Why is the answer in cell E11 1807.  It should either be 1874 i.e. 1807+67 or it should remain 1807 (but then 67 should be there in cell C11).

Please clarify.

[chrismcdonald]

you were correct.. I had made a mistake with the calculations.

I have updated the excel sheet now. Hopefully all correct this time

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[leonahc]

Hello, this file is no longer available on the thread and I have a similar problem. Any chance you still have it to share again?

[Ashish_Mathur]

Hi,

I do not have that file now.

[chrismcdonald]

Hi Ashish,

This is working perfect until I add in the actual as an overlay then I get this little extra bit out the side for the current month. 

Are you able to assist in getting them to align still?

I Hope this makes sense. Cheers

[Ashish_Mathur]

Hi,

Show me the exact change you have made and the result that you are expecting.

[chrismcdonald]

Hi Ashish,

Please see the updated image.

I added the actual data as I am trying to get it to show where the actual finishes and the forecast begins as per attachment.

Thank you again for your assistance