Dynamic column values based on selected date

[msam86]

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.

[rajendraongole1 · Accepted solution]

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.

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

[Anonymous · Accepted solution]

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

Hi @msam86 ,

The Table data is shown below:

Please follow these steps:

1. Create a parameter with numeric range

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

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

[msam86]

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

@Anonymous Did you have the xls file?

Regards

[msam86]

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.

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

Regards

[rajendraongole1 · Accepted solution]

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.

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