Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
msam86
Helper I
Helper I

Dynamic column values based on selected date

Hi guys,
I just started with power bi and I need a help.
I have this data:

Name     Year           Actual year      Status
A             2023         2024                Down
B             2024          2024               Attention
C             2025          2024               Up
D             2026         2024                Up


I need to have a data parameter.

Scenario 1 - The current year should always bring the attention status. However, if I select the current year for 2026, names A,B,C should be down and name D should be attention.

Scenario 2 - If I select the current year to be 2025, names A,B should be down, name C should be attention and name D, up.


I don't have a dashboard yet.
Anyone can help me?

Thanks a lot.

2 ACCEPTED SOLUTIONS
rajendraongole1
Super User
Super User

Hi @msam86 - I have checked as per sample data shared, can you please use the below dax logic

 

create a parameter with numeric range, and specify the year values from 2023 to 2026 with increment 1

 

 

Dynamic Status =
VAR SelectedYear = SELECTEDVALUE('Current Year'[Current Year])
RETURN
SWITCH(
    TRUE(),
    'Testingdata'[Year] < SelectedYear, "Down",
    'Testingdata'[Year] = SelectedYear, "Attention",
    'Testingdata'[Year] > SelectedYear, "Up",
    'Testingdata'[Status]  -- Default to the original status if no condition matches
)
 Ensure the parameter and calculated column are correctly named and referenced in your DAX formula.Adjust the range of years in the parameter to match your dataset.Test the functionality by selecting different years in the slicer and verifying the output in your table visual.

rajendraongole1_0-1717603940000.png

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

Anonymous
Not applicable

Hi @rajendraongole1 ,thanks for the quick reply, I'll add further.

Hi @msam86 ,

The Table data is shown below:

vzhouwenmsft_0-1717640047779.png

Please follow these steps:

1. Create a parameter with numeric range

vzhouwenmsft_1-1717640607012.png

 

2.Use the following DAX expression to create a measure(Be careful not to create calculated columns)

 

Measure = 
VAR SelectedYear = SELECTEDVALUE('Current Year'[Current Year])
RETURN
SWITCH(
    TRUE(),
    YEAR(MAX('Table'[YEAR])) < SelectedYear, "Down",
    YEAR(MAX('Table'[YEAR])) = SelectedYear, "Attention",
    YEAR(MAX('Table'[YEAR])) > SelectedYear, "Up",
    MAX('Table'[Status])  -- Default to the original status if no condition matches
)

 

3.Final output

vzhouwenmsft_2-1717640861477.png

vzhouwenmsft_3-1717640882389.png

Details can be found in the .pbix file. If the problem is solved, please mark @rajendraongole1  and I as the solution.

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Hi @rajendraongole1 ,thanks for the quick reply, I'll add further.

Hi @msam86 ,

The Table data is shown below:

vzhouwenmsft_0-1717640047779.png

Please follow these steps:

1. Create a parameter with numeric range

vzhouwenmsft_1-1717640607012.png

 

2.Use the following DAX expression to create a measure(Be careful not to create calculated columns)

 

Measure = 
VAR SelectedYear = SELECTEDVALUE('Current Year'[Current Year])
RETURN
SWITCH(
    TRUE(),
    YEAR(MAX('Table'[YEAR])) < SelectedYear, "Down",
    YEAR(MAX('Table'[YEAR])) = SelectedYear, "Attention",
    YEAR(MAX('Table'[YEAR])) > SelectedYear, "Up",
    MAX('Table'[Status])  -- Default to the original status if no condition matches
)

 

3.Final output

vzhouwenmsft_2-1717640861477.png

vzhouwenmsft_3-1717640882389.png

Details can be found in the .pbix file. If the problem is solved, please mark @rajendraongole1  and I as the solution.

 

 

Thanks a lot for the help!!! Amazing how power bi works!

 

@Anonymous Did you have the xls file?

Regards

Anonymous
Not applicable

 
msam86
Helper I
Helper I

Hi @rajendraongole1 thanks a lot for your help.

 

I think a made a mistake. I received an error: 

Expressions that generate variable data types cannot be used to define calculated columns.

msam86_0-1717609679858.png

 

msam86_2-1717609772863.png

If you help me one more time, I'll appreciate.

 

Regards

 

 

rajendraongole1
Super User
Super User

Hi @msam86 - I have checked as per sample data shared, can you please use the below dax logic

 

create a parameter with numeric range, and specify the year values from 2023 to 2026 with increment 1

 

 

Dynamic Status =
VAR SelectedYear = SELECTEDVALUE('Current Year'[Current Year])
RETURN
SWITCH(
    TRUE(),
    'Testingdata'[Year] < SelectedYear, "Down",
    'Testingdata'[Year] = SelectedYear, "Attention",
    'Testingdata'[Year] > SelectedYear, "Up",
    'Testingdata'[Status]  -- Default to the original status if no condition matches
)
 Ensure the parameter and calculated column are correctly named and referenced in your DAX formula.Adjust the range of years in the parameter to match your dataset.Test the functionality by selecting different years in the slicer and verifying the output in your table visual.

rajendraongole1_0-1717603940000.png

 

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.