cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
tatlar Frequent Visitor
Frequent Visitor

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

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

4 REPLIES 4
greggyb New Contributor
New Contributor

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

djnww Established Member
Established Member

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 Frequent Visitor
Frequent Visitor

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 Frequent Visitor
Frequent Visitor

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!