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

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