topic Re: Forecast Report in DAX Commands and Tips

Forecast Report

Dimps — Tue, 23 Apr 2024 17:36:02 GMT

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

Re: Forecast Report

Dimps — Tue, 23 Apr 2024 23:10:10 GMT

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

)

VAR CanadaUSD =

SUMX(

MthFxRate,

IFERROR(

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

BLANK()

)

RETURN

CanadaUSD

Re: Forecast Report

Dimps — Tue, 23 Apr 2024 23:11:16 GMT

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

Re: Forecast Report

gmsamborn — Wed, 24 Apr 2024 00:01:45 GMT

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.

Re: Forecast Report

Dimps — Wed, 24 Apr 2024 15:14:02 GMT

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

)

VAR CanadaUSD =

SUMX(

MthFxRate,

IFERROR(

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

BLANK()

)

RETURN

CanadaUSD

Re: Forecast Report

Dimps — Wed, 24 Apr 2024 19:41:26 GMT

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