Forum Discussion
Coloring Legend Category Values
Hi again @mjanecek
Just to clarify, the year_offset column should be configured in the data source so that for each yearnumber it contains the offset (or difference) in years from the most current year, and updates correctly on each refresh.
In other words, the most current year will always have year_offset = 0, the previous year will have year_offset = -1, and so on.
I would assume it should look like this as of today:
yearnumber year_offset 2021 -3 2022 -2 2023 -1 2024 0 and like this when 2025 rolls around:
yearnumber year_offset 2022 -3 2023 -2 2024 -1 2025 0 Once yearnumber has Group By Columns set to year_offset (which it sounds like you've done correctly), when yearnumber is placed on the legend, the formatting will be linked to year_offset values.
In my sample PBIX, I simulated changes in the "current year" by changing the CurrentYear parameter, which then updated the Year Offset column.
Does that help get it working at your end?
Regards
Hi again Mark mjanecek
I've actually adjusted my sample PBIX so that my Date table is created in Power Query based on a CurrentYear parameter also in Power Query, which is a better approximation of your model. (Previously I had a DAX calculated Date table created with Bravo external tool which complicated things.)
The updated PBIX is attached to this post and below I've listed all the setup steps to help checking against your model.
1. I created the parameter CurrentYear in Power Query, and set to 2024 initially. This parameter serves the same purpose as YEAR(GETDATE()) in SQL (except that it is set manually here):
2. I defined the Year Offset column in the Date table in Power Query as [Year] - CurrentYear:
3. In Tabular Editor, for the Year column, I set Group By Columns property to Year Offset:
Tabular Editor 3
Tabular Editor 2
4. I applied a page level filter: -3 ≤ Year Offset ≤ 0:
4. I then created a column chart:
- X-axis = 'Date'[Month], Y-axis = [Sales Amount], Legend = 'Date'[Year]
- Since 2024 initially corresponds to Year Offset = 0, I formatted 2024 as blue and 2021-2023 as shades of grey. I only bothered formatting these four years since I only care about Year Offset -3 to 0 for now.
- For reference, I added a table visual showing Year & Year Offset.
5. Next I simulated different "current years" by changing the Power Query CurrentYear parameter (Transform Data > Edit parameters). Since my dataset ends at 2024 I couldn't test 2025, so I tested 2023 and 2022:
2023:
2022:
Hoping that helps. Is there any step that didn't match what you're doing?
Regards
Hi mjanecek
The Group By Columns property is a handy tool for this situation.
You could:
1. Add a Year Offset column to your Date table, where Year Offset = Year - Current Year in such a way that it will update correctly on each refresh.
Ideally do this in Power Query or further upstream rather than a DAX calculated column.
2. Set the Group By Columns property of the Year column to Year Offset in Tabular Editor. See this SQLBI article for an example of how to set this property.
3. Now when Year is used as a grouping or filtering field in a visual (such as the legend in the column chart), Power BI will translate Year to Year Offset, any any formatting applied based on Year will actually be applied based on Year Offset.
Attached is a small example, where I have a parameter CurrentYear which determines the current year. This would be set up dynamically in reality.
See also this article:
https://owenaugerbi.com/exploring-slicer-default-selections/#Solution-2
Does this work in your model?
Regards
Hi Owen: I created a year_offset column in my calendar table as shown below.
I then assigned yearnumber year_offsett as a group by column in tabular editor.
I populated yearnumber into legend below.
I increased the year limits before and after 2021 and 2024 in my calendar table and fact table and this was the result.
What I want to have is for the 4 colors in the above visual to shift to blue on the most current year (4 year rolling)when the calendar flips.
Instead the colors are staying with the years they had and a new color is added for the current year.
What am I missing?
Hi again @mjanecek
Just to clarify, the year_offset column should be configured in the data source so that for each yearnumber it contains the offset (or difference) in years from the most current year, and updates correctly on each refresh.
In other words, the most current year will always have year_offset = 0, the previous year will have year_offset = -1, and so on.
I would assume it should look like this as of today:
yearnumber year_offset 2021 -3 2022 -2 2023 -1 2024 0 and like this when 2025 rolls around:
yearnumber year_offset 2022 -3 2023 -2 2024 -1 2025 0 Once yearnumber has Group By Columns set to year_offset (which it sounds like you've done correctly), when yearnumber is placed on the legend, the formatting will be linked to year_offset values.
In my sample PBIX, I simulated changes in the "current year" by changing the CurrentYear parameter, which then updated the Year Offset column.
Does that help get it working at your end?
Regards
Can you send me the dax for your current year parmeter?
I tried to test by shifting the years on my calendar and fact tables from '21- '24 to '22- 25 and changing the logic on my year offest column from YEAR(Date)-YEAR(GETDATE()) to YEAR(Date)-YEAR(GETDATE())-1 such that 2022 was -3 23 was -4, 24 was 2 and 25 was 0.
I applied changes and symbology was correct for 22-24 but is not picking up '25 for some reason.
Mark Janecek
Business Intelligence Analyst
Amwins Insuracne
Hi again Mark mjanecek
I've actually adjusted my sample PBIX so that my Date table is created in Power Query based on a CurrentYear parameter also in Power Query, which is a better approximation of your model. (Previously I had a DAX calculated Date table created with Bravo external tool which complicated things.)
The updated PBIX is attached to this post and below I've listed all the setup steps to help checking against your model.
1. I created the parameter CurrentYear in Power Query, and set to 2024 initially. This parameter serves the same purpose as YEAR(GETDATE()) in SQL (except that it is set manually here):
2. I defined the Year Offset column in the Date table in Power Query as [Year] - CurrentYear:
3. In Tabular Editor, for the Year column, I set Group By Columns property to Year Offset:
Tabular Editor 3
Tabular Editor 2
4. I applied a page level filter: -3 ≤ Year Offset ≤ 0:
4. I then created a column chart:
- X-axis = 'Date'[Month], Y-axis = [Sales Amount], Legend = 'Date'[Year]
- Since 2024 initially corresponds to Year Offset = 0, I formatted 2024 as blue and 2021-2023 as shades of grey. I only bothered formatting these four years since I only care about Year Offset -3 to 0 for now.
- For reference, I added a table visual showing Year & Year Offset.
5. Next I simulated different "current years" by changing the Power Query CurrentYear parameter (Transform Data > Edit parameters). Since my dataset ends at 2024 I couldn't test 2025, so I tested 2023 and 2022:
2023:
2022:
Hoping that helps. Is there any step that didn't match what you're doing?
Regards