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