Target Line Based on What-If Parameter

[armchairexpert]

Hi,

I have a line chart with test Carbon Emission data from 2021 to 2025 in my Power BI report. The objective is to go carbon neutral by 2050.

I want to show a target line on the line chart. I want to parameterise the Target Year. So when the user selects a year on the parameter, on the chart it will dynamically show the target line between 2021 and the year the user selected. For example if the user selects 2030 on the parameter, the line should show between 2021 and 2030 so that the user will know we are reaching the CO2 target.

If the user selects a year in the past, then it should show blank.

Can someone please help me create a measure for the target line? I am trying to attach my .pbix file but not sure how to do that as it allows only photos and videos. 

Thanks for your help.

[rohit1991]

Hii @armchairexpert 

You can create a dynamic target line using your What-If parameter by returning a value only for years between 2021 and the selected Target Year. For years before 2021 or after the selected year, the measure will return blank.

Try this measure:

Target Line =
VAR SelectedYear = SELECTEDVALUE(TargetYear[TargetYear])
VAR CurrentYear  = MAX('EmissionData'[Year])
RETURN
IF(
    NOT ISBLANK(SelectedYear)
        && CurrentYear >= 2021
        && CurrentYear <= SelectedYear,
    1,   
    BLANK()
)

Add this measure to your line chart as a separate series.
Now the line automatically extends from 2021 up to the chosen year (e.g., 2030) and disappears when a past year is selected.

[armchairexpert]

Hi @rohit1991 

Thanks for your response.

That showed a straight line. Let me try to explain further.

I have a 'Start CO2 Emission' value of 50.99 for January 2021.

Start CO2 Emission =

CALCULATE (

    [Total CO2 Emission],

    'Calendar'[Year] = 2021,

    'Calendar'[Month Number] = 1

)

So the line should start from 50.99 as per the test data and then gradually become zero for the year the user selects.

Thanks.

[rohit1991]

Hii @armchairexpert 

Please check the complete step-by-step example in the link below. It shows how to start the target line from your 2021 CO₂ value and then gradually decrease it to zero based on the selected Target Year:

https://netorgft12209796-my.sharepoint.com/personal/rohit91_oxygenbi_com/_layouts/15/onedrive.aspx?i... 

[armchairexpert]

@rohit1991 

Thanks for that but it won't let me access your SharePoint.

[rohit1991 · Accepted solution]

Hii @armchairexpert 

Open link and Download file as below in image :

https://netorgft12209796-my.sharepoint.com/:u:/g/personal/rohit91_oxygenbi_com/IQC0O2HXNN_LSazYAM21o... 

[armchairexpert]

Hi @rohit1991 

Yes, that worked. Thanks for taking the time to help me. Much appreciated.

[armchairexpert]

Hi @rohit1991 

Thanks for that. Yes, now I know where I went wrong. Your tables have the Date columns and you have linked using the Date whereas I don't have Date columns in my tables but Year, Month and Quarter. Let me change my tables and confirm. But I am sure that is the issue. I will get back.