How Gold fact tables surface currency amounts. This is a general specification — it applies to every fact table that carries a monetary measure. It covers D365 source column naming, the standard set of currency variants exposed on facts, when each variant is native versus when it is derived by a rate join, and the rate lookup rules.
2. The report currency switch
3. Standard fact column set
4. Sourcing each variant: native vs derived
5. Rate lookup rules
6. Rate calculation (Display Factor)
7. Base plus inverse rate pattern
8. Applying the rate
1. D365 currency column naming
D365 F&O amount columns follow one of two naming conventions depending on the age of the table.
Legacy tables (Axapta-era, most proj*, cost*, sales*, invent* tables)
Suffixes are added directly to the amount column name.
| Suffix | Meaning | Example column names |
|---|---|---|
CUR | Transaction currency — the currency the transaction was captured in | AMOUNTCUR, COSTAMOUNTCUR, SALESAMOUNTCUR, TAXAMOUNTCUR |
MST | Accounting (company) currency — the ledger's own currency, D365 pre-calculates and stores it. Legacy Danish/Axapta naming. | AMOUNTMST, COSTAMOUNTMST, SALESAMOUNTMST, TAXAMOUNTMST |
| (no suffix) | Ambiguous; typically transaction currency but check the table's own conventions | LINEAMOUNT, SALESORDERBALANCE |
Newer tables (spelled-out naming, e.g. generaljournalaccountentry)
Currency is part of the full column name.
| Column name | Meaning |
|---|---|
AmountTransactionCurrency | Transaction currency (equivalent to legacy CUR suffix) |
AmountAccountingCurrency | Accounting currency (equivalent to legacy MST suffix) |
AmountReportingCurrency | Ledger reporting currency — only some tables carry this natively |
CUR and MST pair (or Transaction and Accounting pair). Others store only the transaction-currency amount and any other currency must be derived on the fact. Always check the source schema before deciding whether a rate join is required.
2. The report currency switch
Currency-aware reports let the user select a currency view via a switch driven by dim_currencytype. Exactly five switch values exist.
| # | Switch value | What it represents |
|---|---|---|
| 1 | Reporting | The reporting currency configured on the legal entity's ledger in D365 |
| 2 | Accounting/Local | The accounting currency of the legal entity. Accounting and Local are treated as the same value; a separate "Local" column is legacy and not used on new facts. |
| 3 | Regional | The ledger's regional (secondary) currency |
| 4 | Global | USD (the group's global reporting currency) |
| 5 | GBP | GBP (Head Office reporting) |
3. Standard fact column set
Each amount measure on a fact is exposed in six currency variants: five to match the report switch, plus TransactionCurrency stored alongside for reference and native drill-down. Column naming pattern:
<MeasureName><CurrencyVariant>Currency.
| Fact column suffix | Maps to switch value |
|---|---|
TransactionCurrency | Not switched — native reference |
AccountingCurrency | Accounting/Local |
ReportingCurrency | Reporting |
RegionalCurrency | Regional |
GlobalCurrency | Global (USD) |
GBPCurrency | GBP |
4. Sourcing each variant: native vs derived
Whether a fact column is populated natively from the source or has to be derived by a rate join depends on which columns the underlying D365 table stores. The table below sets out both possibilities per variant. FROM (starting currency) and TO (finishing currency) refer to the rate join direction when the column is derived.
| Variant | Starting ccy (FROM) | Finishing ccy (TO) | Native path (source has it) | Derived path (source lacks it) |
|---|---|---|---|---|
TransactionCurrency |
— | Transaction currency (source-defined) | Read <amount>CUR / Amount…TransactionCurrency straight from source |
N/A — every source that carries a monetary amount carries this variant |
AccountingCurrency |
Transaction currency | Ledger's accounting currency | Read <amount>MST / Amount…AccountingCurrency straight from source |
Multiply Transaction Currency amount by rate (see section 5) |
ReportingCurrency |
Transaction / Accounting currency | Ledger's reporting currency | Read Amount…ReportingCurrency straight from source (only present on newer tables) |
Multiply either Transaction Currency or Accounting Currency amount by rate (see section 5) |
RegionalCurrency |
Transaction / Accounting currency | Ledger's own CurrencyCode (the "regional" or ledger currency) |
Rare / not standard | Multiply either Transaction Currency or Accounting Currency amount by rate (see section 5) |
GlobalCurrency |
Transaction / Accounting currency | Literal 'USD' |
Not standard | Multiply either Transaction Currency or Accounting Currency amount by rate (see section 5) |
GBPCurrency |
Transaction / Accounting currency | Literal 'GBP' |
Not standard | Multiply either Transaction Currency or Accounting Currency amount by rate (see section 5) |
Transaction Currency amount, others from the source's Accounting Currency amount. Match whichever amount column is already convenient in the join, and set the rate lookup's FROM currency accordingly. Exception: the AccountingCurrency variant itself, when derived, must start from Transaction Currency — you cannot multiply an accounting-currency amount to reach the accounting currency.
5. Rate lookup rules
When a currency variant has to be derived, the fact reads exchange-rate rows from Silver's rate tables (a union of exchangerate, exchangeratecurrencypair, and exchangeratetype) and filters them by four criteria.
| Criterion | Value |
|---|---|
| Rate type | BUDGET for all derived variants. This is the T&T convention driven by the report currency switch reflecting a planning view rather than an on-the-day spot view. |
| FROM currency | The starting currency of the conversion — either the source row's Transaction Currency or its Accounting Currency. Must match the amount column being multiplied (see section 4). Exception: when deriving the AccountingCurrency variant itself, FROM must be Transaction Currency. |
| TO currency | Varies per variant — see section 4. |
| Rate date | The transaction's own date (typically TRANSDATE, AccountingDate, or the equivalent business-date on the source row). Never current_date(). |
| Validity window | Transaction date is BETWEEN the rate row's ValidFrom and ValidTo. |
current_date() in a rate join causes historic transactions to re-value every time the fact is refreshed. Always use the transaction's own business date so a posted row keeps the rate it was booked at.
6. Rate calculation (Display Factor)
Raw rates in exchangerate.ExchangeRate are stored with an implied scaling factor recorded in
exchangeratecurrencypair.ExchangeRateDisplayFactor. The scaled rate used for actual conversion is derived from a simple switch on the factor.
| Display Factor | Applied to raw rate |
|---|---|
| 1 | ExchangeRate ÷ 100 |
| 2 | ExchangeRate ÷ 10 |
| 3 | ExchangeRate × 1 (unchanged) |
| 4 | ExchangeRate × 10 |
| 5 | ExchangeRate × 100 |
PySpark implementation:
7. Base plus inverse rate pattern
Exchange-rate pairs are stored directionally (FROM → TO) with only one direction per pair. To resolve conversions in either direction without special-casing, build a union of two DataFrames.
| DataFrame | Contents |
|---|---|
s_rates_base | Pairs as stored in D365 (FROM ccy → TO ccy at scaled Rate). |
s_rates_extra | Inverse of each pair: TO becomes FROM, FROM becomes TO, Rate becomes 1 ÷ Rate. |
s_rates | s_rates_base.unionByName(s_rates_extra). Every rate join on the fact reads from this union. |
Result: a rate join on FromCurrencyCode = <transaction ccy> resolves regardless of which direction the source rate happens to have been recorded in.
8. Applying the rate
The derived column is the transaction-currency source amount multiplied by the resolved rate, with a coalesce fallback so unmatched rates do not null the row.
1, not 0. If no rate matches, the transaction amount passes through unconverted rather than being zeroed. Unconverted values surface in diagnostics as "converted equals native for non-target-ccy rows" — easier to spot than an all-zero column.