Latest Enterprise DNA Initiatives

Multiple Currency - Reporting

Hi Matty,
Not sure what you mean. You can go forward : Press New Table .
Create a new table and use that instead for the Lookup in the measure, that works perfect.

Have a look at the table “GBPExchRateTable” and the measure " Payments GBP DAX Rates 1 Column " at the attached pbix Payment Reporting in different currencies vDAX.pbix (166.4 KB)

1 Like

Thank you @deltaselect,

My mistake was I was pressing a new measure instead of a new table.

It seems strange that we can’t use calculate table in the Variable measure;/

The only thing left I would like to understand why the below DAX pattern can’t fully work if there are 2 or more payments in a different currency:

Test 6 =

VAR _StartMonth = STARTOFMONTH(Sales[Payment Date])

VAR _Currency = SELECTEDVALUE(Sales[Base Curr])

VAR _USD = “USD”

Return

SUMX(Sales,

DIVIDE(Sales[Gross Invoice Amount] ,

LOOKUPVALUE(‘Currency’[FX Amount],

‘Currency’[Date],_StartMonth,

‘Currency’[Currency],_Currency,

‘Currency’[FXto],_USD)))

Thank you,

Matty

Hi @Matty. There’s no problem using CALCULATETABLE within a VAR within a measure, only that a measure cannot return a table, only a scalar value, so the measure will need a later statement to process the virtual table and result in a scalar.
Greg

1 Like

Thank you @Greg,

One last question, What is the reason that Dax can’t handle the below pattern correctly:

Test 6 =

VAR _StartMonth = STARTOFMONTH(Sales[Payment Date])

VAR _Currency = SELECTEDVALUE(Sales[Base Curr])

VAR _USD = “USD”

Return

SUMX(Sales,

DIVIDE(Sales[Gross Invoice Amount] ,

LOOKUPVALUE(‘Currency’[FX Amount],

‘Currency’[Date],_StartMonth,

‘Currency’[Currency],_Currency,

‘Currency’[FXto],_USD)))

but if use similar pattern in the query editor (m-code) : Currency{[Currency = [Base Curr],Date = [Start of Month], FXto =“GBP” ]}[FX Amount] then everything works.
I would like to understand why the Test 6 formula can only convert payments to USD if there are no multiple currencies on that day:

@AntrikshSharma Could I ask for your explanation as one of DNA experts please :slight_smile:

@BrianJ

Could I ask for help, just for an explanation as I don’t understand why the above pattern didn’t work correctly for multi-currencies that occurred on the same day.

Thank you,

Matty