PowerBI Range charts

Question

I am trying to create a range chart as the following:

I followed the video in this link for forecast and it seems that there is a way to set a minimum and maximum for a line chart which will create this affect. The link is the following: PowerBI Link

However I am facing two issues:

1. How shall I create the affect in gray? Where the gray area shows the range between the min and max values between the years 2012-2014.
2. How can I create the logic to take 2012-2014 for the min and max. Should this be done in a DAX expression?

Answer 1

The short answers...

1. I don't think this is easily possible with the built-in visualizations. You'll need a workaround with either two dark lines representing the edges, or abuse a stacked bar chart.

2. The logic to get those lower/upper bounds of the gray area might be possible with DAX, but I think it's easier if you do it in your queries.

---

The longer answer...

Here's the approach I'd take. High level, the suggestion is to create separate data sources for:

1. The 2015+ data (the actual lines).
2. The lower bound of the gray area.
3. The upper bound of the gray area.

Then append those again and plot them in one graph.

Question 2: how to sculpt the data

1. Suppose you have this CSV:

   Date;Val
   2012-01-01;200
   2013-01-01;100
   2014-01-01;150
   2015-01-01;120
   2016-01-01;130
   2012-03-01;190
   2013-03-01;120
   2014-03-01;140
   2015-03-01;130
   2016-03-01;145
   2012-06-01;200
   2013-06-01;130
   2014-06-01;140
   2015-06-01;150
   2016-06-01;155
   

2. Import it into PowerBI.

3. Using the query editor make sure the "Promoted Headers" step is included, and the "Date" column is of the right type.

4. Add an Index

5. Add a custom column: SortableMonthAndDay formula Date.ToText([Date], "MM-dd")

6. Add a custom column: Label formula Date.Year([Date]).

7. Filter the rows on the Date column to be is after or equal to and value 2015-01-01, like this:

   

8. Duplicate the query to one names data-minvalue.

9. In the "Applied Steps" click the gear icon for the filter and change it to before.

10. Doe the Group By action and name the new column Val with operation Min on column Val, like this:

    

11. Add a custom column Label with formula plainly "Lower Bound".

12. Repeat steps 9-11 but this time group by Max and label it "Upper Bound".

13. Choose Append Queries as New in the ribbon, and select all three tables, like so:

    

    You should now see something like this:

    

At this point you're done with the query editing. Basically this was the answer to question number 2 by OP.

Question 1: how to display this

Like I said before, the gray area isn't something that's in PowerBI default visualizations AFAIK. The simplest workaround is probably to use a regular line chart, and choose appropriate colors for your series.

Here's an example:

As you can see, I chose thick black lines for upper/lower bound, and lighter pastel colors for the actual lines.

I'm afraid that other than (a) the above workaround, or (b) custom visualizations, the answer is that it's not possible.