Forum Discussion

TimmHuijsmans's avatar
TimmHuijsmans
Frequent Visitor
3 years ago

Dynamic yearly column names for table headers

Hi,

 

I have looked at all other posts I managed to find related to this problem, but have not been able to figure out whether or not this is something that can be achieved in power BI. Therefore, I'm hoping to get a definite answer on whether this can be done, and if so, how.

 

My problem is as follows: 

I want to compare montly averages of a certain value for the last 6 years that are available in my database. To do this, I made a table where each row is a month, and each column is the year. The table looks as in the screenshot below:

This works for now, but this is going to break as soon as we go into 2024, and I don't want things to break. The reason it is going to break, is that 2018 will no longer be in my dataset. Additionally, this way the new year's column will not be included either.

 

As a test, I updated my query to no longer include 2018, and this is the error I am getting:

 

Basically, what I want is for the visual to work with any combination of 6 (always 6) column names, as these names will change every time we change year. Please note that I have already solved the problem of dynamic column names in Power Query, all I want is for the visuals to accept the changing column names. An important requirement is that I always want the column headers to have the year in the visual, so a solution with column names such as "this year", "last year", etc. won't cut it.

 

Please find the data here:

 

month, 2018, 2019, 2020, 2021, 2022, 2023, monthName
124555.2203222653.6932321374.3280618506.3964520344.0132319358.97903Jan
224711.3946423721.0664320749.9006918540.3628621473.7942919739.93571Feb
323796.6451622519.8483921462.277118339.1520477.2277418677.92143Mar
423702.70222547.1583323753.0766718602.9220560.76267nullApr
523661.6645221340.7558118625.4812918045.5148419693.89935nullMay
625199.72622333.0056716492.9636718489.01319722.65767nullJun
723690.487121482.9345217265.9535519766.1509720598.45903nullJul
824149.6509721657.2851617397.8058118726.8780621576.54484nullAug
923479.22120439.41917937.419644.2916721037.96633nullSep
1023501.3264521948.0916117984.5264519913.6261321213.23935nullOct
1124359.51321943.2093318665.8343320501.1786720769.25767nullNov
1224438.2158121744.9619419475.7296820438.5912920334.66613nullDec

Thanks a lot in advance!

 

Tim

3 Replies

    • TimmHuijsmans's avatar
      TimmHuijsmans
      Frequent Visitor

      @Greg_Deckler, how would I go about pivoting into the required format if the table visual input is in unpivoted format? A requirement is for the table to be displayed as I showed on the photo, with rows as month and year as columns.

       

      Or is this perhaps where the matrix table comes in? In that case, would it be possible to do the same for a timeseries plot of multiple years with years rolling off? So a way to plot multiple timeseries straight from unpivoted data?

       

      Thanks!

      Tim