1 line in a graph : actual and forecast

[Magdelicia]

Hi everyone! 

I'm a beginner in PowerBI and after all my research, impossible for me to solve my problem. 

I want to do a simple thing : having a line which show my actual sales and my futur forecasted sales (already calculated). Same as the picture below.

MAXDATESALES = last date of sales recorded  in DB

I used an if ([MAXDATESALES] > today(), [FORECASTCALCULATION] ; 0)

But powerbi only show me historical sales. I don't know what to do 

[nickchobotar · Accepted solution]

Hello,

Most of the time forecast reports are coming in as separate files (usually as spreadsheets) which is a live document and can be subject to different changes. Best practices here are to include forecast as a separate table and join it to the Date dimension.

*** To my knowledge dotted lines are not yet supported despite numerous requests on ideas.powerbi.com

Thanks, Nick -

[Anonymous]

Hey..

 I tried plotting the same but i am getting discontinuity at the date where actuals end and and forecasted starts. I have tried almost everything. what do you think can be the reason behind this and how to solve it. Thank you

[Magdelicia]

---

fhill wrote:

#Interested #Following!

This is the only way we've been able to 'simulate' this functionality.  We make sure the end of Actual always matched the start of Forecast, and then color code to seperate...  We would love to be able to do the dotted lines and a Vertical marker that moves with a slicer.  FOrrest

Yea ! But i tried Nickchobotar idea and it actually works perfectly !!!

Thanks guys for your help i'm happy now   

[nickchobotar]

@Magdelicia

Good news. August Power BI Desktop release has dotted lines. Now, you can show your forecast as a dotted line.

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

Nick -

[fhill]

#Interested #Following!

This is the only way we've been able to 'simulate' this functionality.  We make sure the end of Actual always matched the start of Forecast, and then color code to seperate...  We would love to be able to do the dotted lines and a Vertical marker that moves with a slicer.  FOrrest

[CindyD]

Hi fhill,

What if I have two lines (two categories) that want to show Actual and Forecast?

In your suggested solution, if I drag the 'Type' to legend, then I cannot have two categories in 'Values'.

Any thought?

Thanks,

CindyD

[fhill]

Cindy,

In this case I would use a hybrid solution between my suggestion Nick's solution.  Instead of using Type as a Legend, leave Legend blank and create Multiple Measures containing FILTER code (One for each Type) and then drag all of these new Measures onto the Values Field.  The end results will be lines that only show up for each Type based on teh FILTERed measure.

If you post some sample data, I can help you with the Measure FILTER commands if needed.

Forrest

[CindyD]

Thanks Forrest! I found a way to cheat it.

Thanks again!

Regards,

Cindy

[nickchobotar]

@CindyD

Great that you got it to work but you got us all intrigued .   Do you mind sharing your solution with the community.

Thanks, Nick -  

[CindyD]

Sure!

I have actual and forecast data all in one file, then I added four more columns to separate the actual and forecast data.

In this way I can drag my added four columns all to the Value field, and just change the forecast lines to a dash format. That gives me what I am after.

Hope that explains!

Cheers ~~ Cindy

[nickchobotar]

@CindyD   Excellent! 

Please remember that you have an option to break your Actuals and Forecast data into separate excel work sheets or even excel files and make a data model. 

Also you could add a Calendar table to your model 

You can create this table by going to Data View -> Calculated Table and  write the following  code Calendar  = CALENDARAUTO() this will create a separate table with one Date field (consecutive dates which Power BI will automatically populates for you) 

All that's left to do after that is to join your Calendar table to Actual and Forecast tables.  (join in the Date field) 

In the report you you will need to use field from Calendar table on the axis. 

[CindyD]

Hi Nick,

Thanks for the showing the details! I learnt something new about calendar table!

But in my specific case, I can't split the data in different files or tabs, as actual and forecast all automatically updated/calculated in one tab. Split them out will create more admin work.

Thanks for sharing your thought!

Cheers ~~ Cindy