Baseline Calculations

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.