Undelining a value in a PowerPivot chart - PowerPivot v2

Question

Hi,

I'm creating a chart with the Date dimension as categories. I'd like to show a separation (by a vertical line?) between what it is sold prior or equal the today date and what it is booked after the today date. Is it possible? In this way, it could be point out the actual data and the forecast data.

Any suggests to me, please?

Many thanks

Answer 1

Hi pscorca -

This isn't exactly what you were asking for, but here's an option that you might use instead.  You can a different series for historical vs future.  Just add a calculated column to your data that you can use to distinguish historical data from future data.  Something like:

=if([date]<TODAY(),"Historical","Forecast")

Then use that calculated column as the legend / series in the chart.  This will allow you to color the future or forecast data differently than the historical data.  I'm trying to upload an image of my example column chart, but seems to be an issue with uploading files at the moment.  Will try again later.  Hope this helps though.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

Hi Brent, really I've created a calculated column in my date table with a similar formula with your one. Then I create a table with "Historical" and "Forecast" row values to feed a slicer. When I choice Historical I can see in my chart only historical series values and so on.

Now, I ask how I can put this formula to have a different colour for historical and forecast values. Is it necessary to create a specific measure?

Thanks

Answer 3

In the field list that drives you pivot chart, you can drop date in the "Axis Fields (Categories)" area (bottom left box).  Then drop your Type attribute (Historical / Forecast) into the "Legend Fields (Series)" area (middle right box).  Then your measure in the Values area.

With that, you'll automatically get different colors for the 2 series you are plotting (distinct values within the Type field: historical, forecast).

Still having issues trying to upload an image of my example.  Hopefully that explanation helps though.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 4

Hi Brent, I put my Type attribute in the series field obtaining a good result.

Also I cannot insert an image (I've tried with the png, jpeg and gif format unsuccesfully). I can load only a partial image.

However, I can see a trend line interrupted for the today value and then it continues from the today + 1 values. It is the right behaviour, isn'it? It seems a really fine behaviour.

Thanks

Answer 5

Glad to hear it worked.  Yes, if you use a line chart, you should have 2 different colored lines for the 2 series with a gap where the dates change from history to future.

Another option, more along the lines of your original question about the axis labels, would be to use the "Type" field as an Axis field (Category) above dates.  This will create a group around the dates to give you the distinction between the 2 sets of dates on the axis, and will also allow you to have a continuous line without the gap created by the multi-series approach.

Hope that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 6

Many thanks Brent for your suggests that I've tried both.

I'd like to show a continuous line with two different colour. My chart is a daily trend and so when I select more one month I could have many date values as data categories rendering unreadable my chart. Perhaps, the better solution is the first option also with the gap for the line serie.

Thanks.

Answer 7

Glad it helped.  Please remember to mark helpful posts and answers.  Thanks.

---

Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

Answer 8

Ok, but however it isn't possible to have a continuous line with two different colours, isn't it?

Thanks

Answer 9

The challenge is you need the last "Historical" value to actually fall into both buckets to make the lines overlap.  Here's how to do it.

Create a calculated measure to give you the last date that is categorized as Historical:

LastDate:=CALCULATE(MAX(sales[date]),ALL(sales),sales[Type]="Historical")

Then create a basic SUM measure and then pivot out your 2 series of data into 2 separate calculated measures based on your SUM and factoring in the LastDate:

SumValues:=SUM(sales[value])

HistoricalSales:=CALCULATE([SumValues],FILTER(sales,sales[date]<=[LastDate]))

FutureSales:=CALCULATE([SumValues],FILTER(sales,sales[date]>=[LastDate]))

The key is the FILTER function that allows the overlap where the last date (because of the >= & <=) actually shows up in both series. 

With those measures defined, you no longer need the Type attribute in the Series area of the chart.  Just drop both measures (Historical and Future) into the Values area, and they will automatically be split into the 2 series lines you need.

Unfortunately I still can't insert an image, but hopefully that explanation helps.  I'll post more details and images in a blogpost shortly.

Hope that helps.

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 10

Hi Brent, this is a very good solution.

I've the Type attribute in the Date dimension, so I've written:

LastDate:=CALCULATE(MAX(sales[date]),ALL(sales),Date[Type]="Historical")

Thanks