cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tatlar
New Member

Highlight current month in yearly view

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):

 

highlight-current-month.PNGExample graph with highlight in gray

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
greggyb Resident Rockstar
Resident Rockstar

Re: Highlight current month in yearly view

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:

 

Capture.JPG

View solution in original post

4 REPLIES 4
Highlighted
greggyb Resident Rockstar
Resident Rockstar

Re: Highlight current month in yearly view

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:

 

Capture.JPG

View solution in original post

djnww Impactful Individual
Impactful Individual

Re: Highlight current month in yearly view

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
New Member

Re: Highlight current month in yearly view

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

tatlar
New Member

Re: Highlight current month in yearly view

Thanks for the comment @djnww. Looks like @greggyb has a solution that may work - I will implement and update the thread. Appreciate the help!

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors