Baseline Calculations

[John_D11]

Hello everyone -

I have created PBI dashboard to show our GHG over several years, and need to create a baseline calculation and compare our progress towards reducing our GHG's.  I have created a measure that uses fiscal year data, however I cant fingure out why it isnt working.  I've scoured the forum for various ideas, and none of them seem to work for me.  Looking for some assisteance.

Current masure is as follows:

Baseline GHG =

Calculate(

    (([Ele CO2 (kg)]+([Ele CH4 (kg)]*[CH4_GWP])+([Ele N2O (kg)]*[N2O_GWP]))*[kg_per_lb])/1000)+

    (([Gas CO2 (kg)]+([Gas CH4 (kg)]/1000*[CH4_GWP])+([Gas N2O (kg)]/1000*[N2O_GWP]))/1000),

    FILTER(

        ALL('Calendar'),

        'Calendar'[Fiscal Year] =

        SELECTEDVALUE('Calendar'[Fiscal Year]=FY21

    )

    )

The 'calculations' work perfectly, and I present the information on a differnt page within the dashboard, however the FILTER piece isnt working.  I have a seperate table for the calendar, and 'Fiscal Year' is one of the available columns. The Fiscal Year is both alpha and numeric (text) in natural and is in the table as shown above in the measure 'FY##' (## representing the last 2 digits of the year).

Appreciate any assistance that you may provide.  

[v-hashadapu]

Hi @John_D11 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[John_D11]

I appreciate your assistance I have been handed a project that requires my immediate attention, so I have had to put this on the back burner so to speak.  I looked at your replies, and I think that they may work.  I will try them when I have more time to dedicate to this project.

Thank You again.

[v-hashadapu]

Hi @John_D11 , sorry to disturb you from your work but can you please conform if your issue if solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @John_D11 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu]

Hi @John_D11 , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu · Accepted solution]

Hi @John_D11 , Thank you for reaching out to the Microsoft Community Forum.

The most efficient approach is to use a direct filter in CALCULATE, which works with your text-based 'Fiscal Year' column. Try below measure to directly filter for FY21, remove unnecessary complexity, and ensure the calculation is performed across all relevant data.

Baseline GHG =

CALCULATE(

    (([Ele CO2 (kg)] + ([Ele CH4 (kg)] * [CH4_GWP]) + ([Ele N2O (kg)] * [N2O_GWP])) * [kg_per_lb]) / 1000 +

    (([Gas CO2 (kg)] + ([Gas CH4 (kg)] / 1000 * [CH4_GWP]) + ([Gas N2O (kg)] / 1000 * [N2O_GWP])) / 1000),

    'Calendar'[Fiscal Year] = "FY21"

)

Since you want to compare progress toward reducing GHG emissions, try below measure to calculate the percentage reduction compared to the FY21 baseline. It calculates the difference between the baseline and current emissions, divided by the baseline, giving you a clear percentage reduction to track progress over time.

GHG Reduction vs Baseline =

DIVIDE(

    [Baseline GHG] -

    CALCULATE(

        (([Ele CO2 (kg)] + ([Ele CH4 (kg)] * [CH4_GWP]) + ([Ele N2O (kg)] * [N2O_GWP])) * [kg_per_lb]) / 1000 +

        (([Gas CO2 (kg)] + ([Gas CH4 (kg)] / 1000 * [CH4_GWP]) + ([Gas N2O (kg)] / 1000 * [N2O_GWP])) / 1000)

    ),

    [Baseline GHG],

    0

)

If you still encounter errors, the issue likely lies in your data model or data. First, verify that the 'Calendar' table is related to your GHG data table via a date column (e.g., 'Calendar'[Date] to 'GHG_Data'[Date]), with a single-directional relationship from 'Calendar' to the data table. Next, test a simple measure like CALCULATE([Ele CO2 (kg)], 'Calendar'[Fiscal Year] = "FY21") to ensure your individual measures return values for FY21. Finally, confirm there’s data for FY21 by checking DISTINCT('Calendar'[Fiscal Year]) in a table visual, and ensure 'Fiscal Year' is set as text in Power BI. These steps will resolve any underlying issues.

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[Ashish_Mathur]

Hi,

I do not know what you want to do but try this

Baseline GHG =
Calculate(
    (([Ele CO2 (kg)]+([Ele CH4 (kg)]*[CH4_GWP])+([Ele N2O (kg)]*[N2O_GWP]))*[kg_per_lb])/1000)+
    (([Gas CO2 (kg)]+([Gas CH4 (kg)]/1000*[CH4_GWP])+([Gas N2O (kg)]/1000*[N2O_GWP]))/1000),'Calendar'[Fiscal Year]=FY21))

[Ashish_Mathur]

Hi,

I do not know what you want to do but try this

Baseline GHG =
Calculate(
    (([Ele CO2 (kg)]+([Ele CH4 (kg)]*[CH4_GWP])+([Ele N2O (kg)]*[N2O_GWP]))*[kg_per_lb])/1000)+
    (([Gas CO2 (kg)]+([Gas CH4 (kg)]/1000*[CH4_GWP])+([Gas N2O (kg)]/1000*[N2O_GWP]))/1000),'Calendar'[Fiscal Year]=FY21))

[rohit1991]

Hi @John_D11 ,
It looks like your baseline calculation measure is almost there, but the issue lies in the way the FILTER and SELECTEDVALUE functions are structured within your DAX. Specifically, the syntax inside the SELECTEDVALUE function is incorrect—you're comparing 'Calendar'[Fiscal Year] = FY21 inside SELECTEDVALUE, which isn't valid. Instead, you should directly pass the value "FY21" (as text) to compare within the FILTER. Here's a corrected version of your measure’s filter logic:

FILTER(
    ALL('Calendar'),
    'Calendar'[Fiscal Year] = "FY21"
)

This change ensures that the filter is correctly identifying only the rows for fiscal year FY21 as the baseline. Make sure that the Fiscal Year column in your 'Calendar' table is formatted as text to match the comparison string. Once this fix is applied, your calculation should work as intended and allow you to compare actuals to the baseline year properly.

[John_D11]

@rohit1991  Thank you for the suggestion.  It still gives me an error.  

I've made sure that the 'Calendar'[Fiscal Year] column is text, and yet no solution as of yet.  It shouldnt matter that the measures are not in the same table as the calendar, correct?

[Greg_Deckler]

@John_D11 SELECTEDVALUE('Calendar'[Fiscal Year]="FY21"

[John_D11]

I appreciate the reply.  It didn't change anything as I still get an error message.  I have the measures in a seperate table from the calendar, does this make a difference at all?