Dynamic currency conversion + context + YTD

Hi,
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?

Hi @flutterbybee, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Hi @flutterbybee, we’ve noticed that no response has been received from you since the 11th of November. We just want to check if you still need further help with this post? In case there won’t be any activity to post the demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details in the next few days, we’ll be tagging this post as Solved. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the checkbox. Thanks!