Forum Discussion
Dynamically changing columns
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?
hi Anonymous ,
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
3 Replies
hi Anonymous ,
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
Hi Anonymous -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!!Hi Anonymous ,
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 TeamIf 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!
