I have divisions A, B, and C who all like to report their values in a different currency
I have all of my amounts in USD and I know how to apply the currency conversion based on the preferred currency of each division. This works perfectly for calculations directly on the amount.
However, supposing I have an amount for division A in April, June, July, I can do a YTD calculation in the appropriate (correct) currency for the months where there is actually data. For the rest of the months, it defaults to the “unknown currency - which happens to be USD”. The issue is the amounts are all calculated correctly in the currency, but if I want to apply a formatter to the result there is no context for division (I assume because there is no data in that month), this means that my formatter can’t work out the currency symbol to apply…
My preferred currency dax looks to see what the lowest level is in scope (and not surprising it’s the underlying row of data), but if I try to access the selectedvalue for that level for the division, the result is blank.
My dax looks something like this (because of course what I have is more complex, and I’m trying to simplify)
Preferred Currency =
if (isinscope(revenue[division]), lookupvalue(division[preferred currency], division[name], selectedvalue(revenue[division])), [company currency])
Can someone help me with how to get out of the pickle I’m in?