I have a document where I have SalesInvoicelines with Company, Datalevel and LineAmount. This LineAmounts are divided in ActualAmount and BudgetAmount depending on DataLevel. I have also a currency-rate because the amount can be in another currency like USD.
So in my script I have two calculations:
if (DataLevel = 'Budget' and SalesInvoiceLineNumber > 0,(LineAmount/DWHConsolitationCompCurrencyExchangeRateToCurrency),NULL()) as BudgetAmount,
if (DataLevel = 'Actual' and ( SalesInvoiceLineNumber = 0 or SalesInvoiceLineNumber > 12),(LineAmount/DWHConsolitationCompCurrencyExchangeRateToCurrency),NULL()) as ActualAmount,
I'm also loading a currencytable with different currencies. In the script I have a tab where a load that qvd and create a currencytable
On my Dashboard I have several tables with dimensions like Year, Month, Company etc. I use als also different expressions to calculate Actual and Budget.