cancel
Showing results for
Did you mean:
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):

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

1 ACCEPTED SOLUTION

Accepted Solutions
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:

4 REPLIES 4
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:

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

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

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!

Announcements

#### 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!

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

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.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

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