Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Want to name columns based on year

I am trying to name headers based on the year.  I need to count work for an area for a year.  in 2019 that was 2019, 2018, 2017 and 2016.  now in 2020 I want the columns to be 2020, 2019, 2018, 2017.  I don't want to have to rename them every year.  I am using this in a stacked column chart and a matrix.  Thanks for any help anyone can provide.

2 REPLIES 2
dm-p
Super User
Super User

Hi @Anonymous,

Without being able to see your data, I can't produce specific DAX relevant to your data model, but if you want your visuals to 'roll' the last few years, I'd approach it as follows:

  1. Add a column to your date table that calculates difference in years between the current year and that date.
    • For your desired ranges, I would expect this to evalute to something like for the following for dates in each year as follows (using my date table as an example):
      image.png
    • Every time I refresh my data, this will re-calculate. So, when 2021 rolls around, everything will shift so that 2021 = 0, 2020 = 1, etc.
  2. Add this column as a filter to your visual (or page if you want all visuals on the page to work from it) and set it to filter where this is less than or equal to the value you want. Using your example, this would be less than or equal to 3 and greater than or equal to 0 e.g:
    image.png

If I simulated the year to be 2019, the above visual would look as follows:

 

image.png

Another such approach could be to have the calculated column return TRUE/FALSE if the year falls inside your business logic. You could also implement with a measure rather than a calculated column, but I find this gives me more flexibility, particularly if I want to change the number of years using a what if paramater (which would allow my users to change the number of years dynamically).

Hopefully this gives you some ideas. Good luck!

Daniel


If my post helps, then please consider accepting as a solution to help other forum members find the answer more quickly 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


On how to ask a technical question, if you really want an answer (courtesy of SQLBI)




Anonymous
Not applicable

Thank you for your reply.  My situation is a lot more complicated because I have many columns in my table which prevent me from using calendar year as a matrix column, so I have 3 rows and 12 values, 5 of which I am trying to make dynamic headers with.  I'm not sure it's possible, but I have a field in the data which has what I want to use as headers, I just don't know how to use it.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.