Dynamically changing columns

[Pragadeesh]

Hi

I have a requirement where I want to change my columns dynamically.
Please refer the table below

Partner	2024 Delivery	2023 Delivery	2022 Delivery
a	10	25	30
b	30	35	20
c	50	45	15
d	60	25	10
e	70	15	20
f	80	25	10

I want my sheet to display only the last three years delivery data. For eg. In 2024, I want three columns from 2022 - 2024. In 2025, I would need data from 2023-2025 and so on. A new column has to be added and the last column has to be replaced by the next year.

I'm trying to do this as part of a migration where they have used the below logic for their tables,
=if(ReviewYear = vmaxyear, Delivery Rating, '') - For the current year
=if(ReviewYear = vmaxyear - 1, Delivery Rating,'') - For the previous year
=if(ReviewYear = vmaxyear - 2, Delivery Rating,'') - for the year before that and so on.

Can someone suggest if and how this can be achieved in Power BI please?

[Anonymous]

Hi @Pragadeesh ,

Thanks for the reply from rajendraongole1/ danextian .

You can use relative date slicer.

Here is the sample data I created:

partner	Value	Date	Year
a	30	1/1/2022	2022
b	20	1/1/2022	2022
c	25	1/1/2022	2022
d	10	1/1/2022	2022
e	20	1/1/2022	2022
f	10	1/1/2022	2022
a	25	1/1/2023	2023
b	35	1/1/2023	2023
c	45	1/1/2023	2023
d	25	1/1/2023	2023
e	15	1/1/2023	2023
f	25	1/1/2023	2023
a	10	1/1/2024	2024
b	30	1/1/2024	2024
c	50	1/1/2024	2024
d	60	1/1/2024	2024
e	70	1/1/2024	2024
f	80	1/1/2024	2024
a	12	1/1/2025	2025
b	32	1/1/2025	2025
c	42	1/1/2025	2025
d	52	1/1/2025	2025
e	12	1/1/2025	2025
f	22	1/1/2025	2025

Use a matrix visual to display the data and use the relative date slicer, as shown below:

For more information on using the relative date slicer see:

Create a relative date slicer or filter in Power BI - Power BI | Microsoft Learn

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[danextian · Accepted solution]

hi @Pragadeesh ,

Your data is not in the best format and it would be real pain to use the same table structure for data model. I would:

• select partner and then unpivot all other columns
• 
• extract the year from the resulting attribute column
• 
• load the query
• create a calculated column to get the max year as per the table and compare each row of year to the max year to return only  those that are >=MaxYear-2 and use this calculated column as the filter.
• 
• 

  Please see attached sample pbix for reference

[rajendraongole1]

Hi @Pragadeesh -To select the columns based on the current year dynamically. Below dax function works, 

Please try below measure

CurrentYear = YEAR(TODAY())

Current Year:

Table_CurrentYear =
IF(
MAX('Table'[Year]) = [CurrentYear],
'Table'[Table Rating],
BLANK()
)

Previous Year

Table_PreviousYear =
IF(
MAX('Table'[Year]) = [CurrentYear] - 1,
'Table'[Table Rating],
BLANK()
)

2 YearBefore

Table_YearBefore =
IF(
MAX('Table'[Year]) = [CurrentYear] - 2,
'Table'[Table Rating],
BLANK()
)

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!