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 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
  • 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

5 Replies

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

      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

    • 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