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
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
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.

Highlighted
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
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors