Forum Discussion
Dynamically changing columns
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 ,
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!