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