Forum Discussion

Dimps's avatar
Dimps
Frequent Visitor
2 years ago
Solved

Forecast Report

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 Ra...
  • Dimps's avatar
    Dimps
    2 years ago

    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's avatar
Dimps
Frequent Visitor
2 years ago

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