Dynamic coloring of latest Year in line chart

[rishtinz]

I have data for different fiscal years and I am using line chart in Power BI.

I have Weeks on x-axis and fiscal year on legend. I would like to have a fixed color (say BLUE) for the most recent year. Currently this is defined manually in the properties and next fiscal year 2022/23 will stay blue and Power BI will pick a new color from the theme pallette for 2023/24. How can I have a fixed color to whatever the most recent fiscal year is. 

I do have a column that determines if the date is current fiscal year called IsCurrentFY.
There's a method to add multiple measures but it won't allow me to do that since I have a legend in place.

[MarkLaf]

Unless you want to get into Deneb or other custom visuals, as far as I know, you can either:

• Go the measures route, where you add a measure for each year and set style manually. You don't use a legend with this approach. You can create a Max version of the measure that dynamically shows max year and which you would never remove from the visual (and thus don't have to redo the red/thickness/whatever styling of the max year line when new data comes in).
• Use two visuals. One is set up how you already have it with a legend, and then the second should be in front, background transparent + all visual components turned off, and aligned with the main visual. Here you would use a 'Max  Year' version for your line and set style accordingly.

Will show both methods with following dummy data (data spans years 2023-2025):

Table

Date	Value
1/1/2023	135
5/5/2023	752
10/20/2023	259
1/5/2024	750
4/10/2024	423
7/10/2024	100
11/20/2024	800
2/13/2025	358
3/5/2025	123
6/20/2025	854

Dates

Dates = 
GENERATE(
    CALENDARAUTO(),
    ROW(
        "Year", YEAR( [Date] ),
        "Week", WEEKNUM( [Date] )
    )
)

Model

Measures approach

My data has years 2023-2025, so I'll create a measure for each year. Assuming we are just interested in SUM of Values for this scenario. I assume we want them to work with a date slicer.

Value 2023 = CALCULATE( SUM( 'Table'[Value] ), KEEPFILTERS( Dates[Year] = 2023 ) )

Value 2024 = CALCULATE( SUM( 'Table'[Value] ), KEEPFILTERS( Dates[Year] = 2024 ) )

Value 2025 = CALCULATE( SUM( 'Table'[Value] ), KEEPFILTERS( Dates[Year] = 2025 ) )

Also, I want a measure that will always show SUM for the max year in data + selected.

Value Max = 
VAR _maxYearSelected = 
    CALCULATE( MAX( Dates[Year] ), ALLSELECTED( Dates ) ) 
RETURN 
    CALCULATE( 
        SUM( 'Table'[Value] ), 
        KEEPFILTERS( Dates[Year] = _maxYearSelected ) 
    )

Now, I'll add all these measures to my line visual with Dates[Week] in the X-axis. Note that we aren't putting anything in Legend. The legend is constructed from the measures.

I'll leave the defaults on the line formatting except for [Value Max], which I'll make red and a little thicker.

This gives us the following, which will work with Dates slicers. This works pretty well, but can be annoying as you need to update your measures as new years come into data.

Two visuals approach

Create the same MaxYear measure as with other approach. The trick here is keeping everything aligned, which, to be safe, means setting up measure or using constants for your x-axis and y-axis ranges.

First, same MaxYear measure:

Value Max = 
VAR _maxYearSelected = 
    CALCULATE( MAX( Dates[Year] ), ALLSELECTED( Dates ) ) 
RETURN 
    CALCULATE( 
        SUM( 'Table'[Value] ), 
        KEEPFILTERS( Dates[Year] = _maxYearSelected ) 
    )

Then, let's create our two visuals. One is set up with a legend and implicit sum. We'll use Dates[Week] as X-axis and Dates[Year] as our Legend.

Next, we want the same visual except just with our Max measure and no legend.

Before aligning, we need to ensure that the axis ranges on both visuals will always be the same. We can manually set X-axis as we are dealing with weeks, Y-axis min we can set to 0, but we'll need the following measure for Y-axis max.

Year Week Max Value = 
CALCULATE(
    MAXX(
        SUMMARIZE( Dates, Dates[Year], Dates[Week] ),
        CALCULATE( SUM( 'Table'[Value] ) )
    ), 
    ALLSELECTED( Dates ) 
) * 1.05

So, on both visuals, axis ranges should look like this.

Now put our max visual on top of the legend visual, make background transparent, and remove all titles, gridlines, and labels. Put on top of the Legends visual and align your max year line. It can be helpful to zoom in and temporarily turn gridlines on to get the alignment just right.

This works and we don't have to do anything different as new years come into data unlike with measures approach. But, it's a little janky and users trying to interact with it may get a little confused as they will be interacting with the mostly transparent front visual depending on where they hover/click. If it were me, I'd put a transparent shape or something in front of the whole thing so there is consistent lack of interaction.

[jake_working]

@EthanH & @rishtinz I ended up with a workaround that may not apply to your specific instance.

First, I created two measures: one that captures the current year [current_year] and another that is the previous year [previous_year]. You may end up using a SELECTEDVALUE or a LOOKUPVALUE in some capacity to use that 'IsCurrentFY' value.

Then, I created two more measures: one that calculates the value I want to plot for the currrent year [current_year_value] and another for the previous year [previous_year_value]. The 'value' for your example looks like sales or something. So, simply calculate the sales but filter only by the recent year/previous year for the two measures.

Then, you can just pop those two last measures into a line chart and set the default color for [recent_year_value] as blue and [previous_year_value] as red. 

To solve the legend issue, I just made two cards that display [current_year] and [previous_year] and make their text colors match the line chart. Place those bad boys near/on the chart in empty space and you should be good to go!

Jake

[jake_working]

Any update or solution provided by somebody? I am having trouble and might need to shift how the chart is created instead.

[EthanH]

Did you find a solution? I'm having the same issue.

[Arul]

@rishtinz ,

Is IsCurrentFY a column? If so, what is the value in that column?

Thanks,

[rishtinz]

The value of IsCurrentFY is 1 or 0.