Dynamic Column for values by year

[NimaiAhluwalia]

Hello

I have some quantity in a column and a date assosiated to it.

i want to calculate and make a dynamic calculated measure in my visual which when more years will populate my matrix should also populate in the below fashion with increasing years of data. i dont want to create seperate dax measures or all the years.( it should automaticaly populate in my visual when a new year with a quantity comes in.

Target output format 

if it is not possible in a measure can we do it on a calculated column, i guess we can do it with the help of addcolumns dax not sure?

@amitchandak 

[Anany · Accepted solution]

Hi Nimai,

I'm not sure if that is possible in Power BI but there is a workaround for this.  I've tried to recreate your use case with sample data and this is what I've achieved.

You need to create an additional KPI table for this- I've added the values in the year column up to 2030 (you can add more if required).

If there were sales available for the particular year then one column will be automatically added to this matrix.

For ex- If you add the Sales for 2025 in the sample excel sheet and hit refresh it will automatically add one column in the matrix named "2025" under sales which will show the sales for the year 2025.

I hope this suffices your requirements. Thanks

Files-

https://docs.google.com/spreadsheets/d/1edW2FHS2qltGZiJ8TUmS3JAbGgo29le-/edit?usp=sharing&ouid=10170...

https://drive.google.com/file/d/1kNKmA8synCCvomaUrnJtpUxJBBmiSs7N/view?usp=sharing

[amitchandak]

@NimaiAhluwalia , if you have joined your data with date table having year. You can take the year on the column of matrix.

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[NimaiAhluwalia]

Sure i will explain with proper example

I am using a table visual not a matrix visual.

I have my data of quantity with diffrernt dates.

My traget is to create a dynamic calculation in a way when new year adds in my data i should get a new column with that year in my visual.

Currently i am creating seperate measures for each year and putting in the visual which i cannot create for every year in future, so wanted to make it dynamic.