- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Integrations with Files and Services
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Highlight current month in yearly view

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

Highlighted

tatlar

Frequent Visitor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-29-2015
12:34 PM

I need to highlight the current month (background or overlay or additional column in gray) in a view that shows data for a whole year. Example attached below (assumes the current month is 3/1/2016):

Is this possible? The highlighted month would obviously change to be 2/1/2016 if the chart was viewed in February 2016, and 4/1/2016 if the chart was viewed in April 2016.

Thanks in advance!

Solved! Go to Solution.

Report Inappropriate Content

Message 1 of 5

1 ACCEPTED SOLUTION

Accepted Solutions

greggyb

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-29-2015
03:17 PM

A bit hacky, but you could just make a measure that only displays in the current month, then put that measure on a combo chart with your actual series.

The measure you'd need looks like this:

CurrentMonth = IF( HASONEVALUE( 'Date'[YearMonth] ) ,IF( VALUES( 'Date'[YearMonth] ) = LOOKUPVALUE( 'Date'[YearMonth] ,'Date'[Date] ,TODAY() ) ,<expression for height> ) )

You could just put 1 as your height expression and use two y axes, but you'll end up with an extra axis on one side ranging from 0 to 1. If you want to plot them on the same y axis, then you'd need to define something that's larger than the values in your line series. I used something like the following:

CurrentMonth = IF( HASONEVALUE( 'Date'[YearMonth] ) ,IF( VALUES( 'Date'[YearMonth] ) = LOOKUPVALUE( 'Date'[YearMonth] ,'Date'[Date] ,TODAY() ) ,MAXX( ALL( 'Date'[YearMonth] ) ,CALCULATE( SUM( FakeData[Value] ) ) ) * 1.25 ) )

The only difference is the MAXX() in there. It finds the largest monthly value of FakeData[Value] and then multiplies it by 1.25 This guarantees it's larger than my line series (which is just SUM( FakeData[Value] )), but still proportional, allowing it to fit well on the same y axis.

The measure is fairly simple. First we check if there's exactly one value for [YearMonth] in context (necessary for the next IF() function).

Then we evaluate VALUES( 'Date'[YearMonth] ) - this gives us all distinct values of that field in context, if there's only 1, then it is cast as a scalar value implicitly, allowing us to compare with the result of LOOKUPVALUE() for equality.

LOOKUPVALUE() just returns the value of [YearMonth] where [Date] = TODAY(). If the [YearMonth] in context is the same as today's, we return the MAXX() or whatever other expression you used there. If not, we return blank. If there's more than one [YearMonth] in context (like in a subtotal of any sort), the measure returns blank.

Here's a sample PBI report where I've implemented this:

4 REPLIES 4

greggyb

New Contributor

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-29-2015
03:17 PM

A bit hacky, but you could just make a measure that only displays in the current month, then put that measure on a combo chart with your actual series.

The measure you'd need looks like this:

CurrentMonth = IF( HASONEVALUE( 'Date'[YearMonth] ) ,IF( VALUES( 'Date'[YearMonth] ) = LOOKUPVALUE( 'Date'[YearMonth] ,'Date'[Date] ,TODAY() ) ,<expression for height> ) )

You could just put 1 as your height expression and use two y axes, but you'll end up with an extra axis on one side ranging from 0 to 1. If you want to plot them on the same y axis, then you'd need to define something that's larger than the values in your line series. I used something like the following:

CurrentMonth = IF( HASONEVALUE( 'Date'[YearMonth] ) ,IF( VALUES( 'Date'[YearMonth] ) = LOOKUPVALUE( 'Date'[YearMonth] ,'Date'[Date] ,TODAY() ) ,MAXX( ALL( 'Date'[YearMonth] ) ,CALCULATE( SUM( FakeData[Value] ) ) ) * 1.25 ) )

The only difference is the MAXX() in there. It finds the largest monthly value of FakeData[Value] and then multiplies it by 1.25 This guarantees it's larger than my line series (which is just SUM( FakeData[Value] )), but still proportional, allowing it to fit well on the same y axis.

The measure is fairly simple. First we check if there's exactly one value for [YearMonth] in context (necessary for the next IF() function).

Then we evaluate VALUES( 'Date'[YearMonth] ) - this gives us all distinct values of that field in context, if there's only 1, then it is cast as a scalar value implicitly, allowing us to compare with the result of LOOKUPVALUE() for equality.

LOOKUPVALUE() just returns the value of [YearMonth] where [Date] = TODAY(). If the [YearMonth] in context is the same as today's, we return the MAXX() or whatever other expression you used there. If not, we return blank. If there's more than one [YearMonth] in context (like in a subtotal of any sort), the measure returns blank.

Here's a sample PBI report where I've implemented this:

djnww

Established Member

Re: Highlight current month in yearly view

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-29-2015
03:18 PM

Hi @tatlar,

I don't think it's possible given the existing charts that are available. Surely staff can hover their mouse pointer over the month they are interested in ?

The only workaround solution that I can think of is to have 2 charts, with the current month overlaid on top of the yearly chart.

UPDATE:

SORRY... tried and it doesn't work. I thought I could get the front graph to show its relative position. But it just centres the current month in the middle of the chart... my bad

Cheers,

Daniel

tatlar

Frequent Visitor

Re: Highlight current month in yearly view

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-29-2015
05:20 PM

Thanks @greggyb! I will look into that - although hacky looks like it solves the problem! Much obliged.

tatlar

Frequent Visitor

Re: Highlight current month in yearly view

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-29-2015
05:21 PM