Forecast Report

[Dimps]

I have the forecast data for Canada and US in USD. I am using DIM_FIX_RATE table for converting USD into CAD. In that table i have the FX Rate from Dec 2019 onwards. it has two columns Date and FX Rate (USD-CAD). CAD numbers are calculating correcly with the below DAX formula till April 2024. Since I dont have the rae for future months CAD column is coming balnk. How can I use the April month average FX Rate for future months.  This is how it is showing for future months which is incorrect.

Amount (CAD) =

VAR MthFxRate =

    ADDCOLUMNS(

        SUMMARIZE(

            DIM_DATE,

            DIM_DATE[Month Date]

        ),

        "Mth FX Rate",

        IF(

            MIN(DIM_DATE[Month Date]) < TODAY(), // Check if the date is before the current month

            CALCULATE(

                AVERAGE(DIM_FIX_RATE[FX Rate (USD-CAD)]),

                ALLEXCEPT(

                    DIM_DATE,

                    DIM_DATE[Month Date]

                )

            ),

            CALCULATE(

                AVERAGE(DIM_FIX_RATE[FX Rate (USD-CAD)]),

                FILTER(

                    ALL(DIM_DATE),

                    DIM_DATE[Month Date] > TODAY () // Starting from May 2024 onwards

                )

            )

        )

    )

VAR CanadaUSD =

    SUMX(

        MthFxRate,

        IF(

            ISBLANK([Mth FX Rate]), // Check if FX rate is blank (for future months)

            // If future month, use forecasted exchange rate

            SUM('smartpnl'[RL Amount (USD)]) / CALCULATE(COUNTROWS(DIM_DATE), ALL(DIM_DATE)),

            // If historical month, use historical exchange rate

            SUM('smartpnl'[RL Amount (USD)]) * [Mth FX Rate]

        )

    )

RETURN

    CanadaUSD

[gmsamborn]

i @Dimps 

I would like to help but without sample data I would just be guessing.

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...

A .pbix file with sample data would be best.

[Dimps]

Thanks @gms !

I am unable to share the pbix file with you through OneDrive, Dropbox, Google Drive. My sample pbix file is ready, if you can share your email address with me, I can send it through email.  To give you more info, this is the main table SS. 

I have the DIM_FIX RATE table until 04/23/2024.

This is DIM_DATE table SS.

RL Amount ($USD) = SUM('Sheet1'[RL Amount (USD)]), for calculating RL Amount (CAD) I am using below DAX . The issue I am having is DIM_FIX_RATE table has the conversion rate until 04/23/2024 and dataset has the forecast numbers in USD until Dec 2025, I need to calculate the CAD values for future months using average rate of the current month. Thank you!

RL Amount (CAD) =

VAR April2024AvgRate =

    CALCULATE(

        AVERAGE(DIM_FIX_RATE[FX Rate (USD-CAD)]),

        FILTER(

            DIM_FIX_RATE,

            MONTH(DIM_FIX_RATE[Date]) = 4 && YEAR(DIM_FIX_RATE[Date]) = 2024

        )

    )

VAR MthFxRate =

    ADDCOLUMNS(

        SUMMARIZE(

            DIM_DATE,

            DIM_DATE[Month]

        ),

        "Mth FX Rate",

        IF(

            MIN(DIM_DATE[Date]) <= TODAY(),

            IF(

                ISBLANK(April2024AvgRate),

                AVERAGE(DIM_FIX_RATE[FX Rate (USD-CAD)]),

                April2024AvgRate

            ),

            April2024AvgRate

        )

    )

VAR CanadaUSD =

    SUMX(

        MthFxRate,

        IFERROR(

            SUM('Sheet1'[RL Amount (USD)]) * [Mth FX Rate],

            BLANK()

        )

    )

RETURN

    CanadaUSD

[Dimps · Accepted solution]

Thank you 🙏 @gmsamborn for your help, I really appreciate it! You are the BEST!!

The 2 extra measures were created [Latest Rate] and [gms2] to have the values in CAD for future months to automatically looks for the latest rate and averages all rates for that month.

- Latest Rate =

    CALCULATE(

        MAX( 'DIM_FIX_RATE'[Date] ),

        ALL( 'DIM_FIX_RATE' )

    )

- gms 2 =

VAR _Latest = [Latest Rate]

VAR April2024AvgRate =

    CALCULATE(

        AVERAGE(DIM_FIX_RATE[FX Rate (USD-CAD)]),

        FILTER(

            ALL( DIM_FIX_RATE ),

            MONTH( DIM_FIX_RATE[Date] )       = MONTH( _Latest )

                && YEAR( DIM_FIX_RATE[Date] ) = YEAR( _Latest )

        )

    )

VAR MthFxRate =

    IF(

        MAX(DIM_DATE[Date]) <= TODAY(),

        AVERAGE( DIM_FIX_RATE[FX Rate (USD-CAD)] ),

        April2024AvgRate

    )

VAR CanadaUSD =

    SUMX(

        'Sheet1',

        'Sheet1'[RL Amount (USD)] * MthFXRate

    )

RETURN

    CanadaUSD

[Dimps]

I changed the formula but still future month CAD values are not populating. May 2024 onwards is blank.

RL Amount (CAD) =

VAR April2024AvgRate =

    CALCULATE(

        AVERAGE(DIM_FIX_RATE[FX Rate (USD-CAD)]),

        FILTER(

            DIM_FIX_RATE,

            MONTH(DIM_FIX_RATE[Date]) = 4 && YEAR(DIM_FIX_RATE[Date]) = 2024

        )

    )

VAR MthFxRate =

    ADDCOLUMNS(

        SUMMARIZE(

            DIM_DATE,

            DIM_DATE[Month]

        ),

        "Mth FX Rate",

        IF(

            MIN(DIM_DATE[Date]) <= TODAY(),

            IF(

                ISBLANK(April2024AvgRate),

                AVERAGE(DIM_FIX_RATE[FX Rate (USD-CAD)]),

                April2024AvgRate

            ),

            April2024AvgRate

        )

    )

VAR CanadaUSD =

    SUMX(

        MthFxRate,

        IFERROR(

            SUM('smartpnl'[RL Amount (USD)]) * [Mth FX Rate],

            BLANK()

        )

    )

RETURN

    CanadaUSD

[Dimps]

DIM_FIX_RATE table has the exchange rates until april 14, 2024 but the forecast values are until Dec 2025. I want to calculate CAD values based on the april month average rate