Forum Discussion

tatlar's avatar
tatlar
New Member
10 years ago
Solved

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): ...
  • greggyb's avatar
    10 years ago

    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: