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