cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## currency | apply a calculated mesure for each currency

Hello,

I need a bit of help to get a solution on my problem.

I have created a measure which calculate an average of for each currency in my file, based on the min & max of selected date.

In the below example, my measure do a sum of all the fixing between those 2 dates / divide by numberof days.

NB : i cannot apply average function because my table Fixing only have dates when i have a fixing - nothing for weekend

Here my formula :

``````test_average fixing =
Var _ccy = DISTINCT(ECB_Fixing[Currency])
VAR _Endate = [MaxSelection]
VAR _Startdate = [MinSelection]
var _SumFX = CALCULATE(SUM(ECB_Fixing[Spot (against USD)]),
ECB_Fixing[Date] >= _Startdate,
ECB_Fixing[Date] <= _Endate)
var _SumFixing = CALCULATE(COUNTROWS(ECB_Fixing),
ECB_Fixing[Date] >= _Startdate,
ECB_Fixing[Date] <= _Endate)

RETURN CALCULATE(DIVIDE(_SumFX,_SumFixing),'Currency'[Currency]=_ccy)``````

see the result : works well

However, if i want to convert a outstanding utilisation, the measure doesnt refer to the currency of each line anymore, my report applies the total "0.464196" and multiple the sum by this amount, which is creating wrong values.

``_OutstandingUSD = [_OutstandingOpenItems]*[testMonth Average FX_selected]``

is there a way to make sure the report will apply the calculated measure of the currency to the respective line ?

I.e : sum of my CHF amount * test_average fixing of CHF ?

thanks, O.

3 REPLIES 3
Frequent Visitor

hi both, thanks for your help. @some_bih i went through the link and it helped me a lot to get it. however, im facing a last issue with my average. if my time range contains date having no fixing, it returns 0, hence decrease my averge utilisation.

I.e : on 1/9, i have an average utilisation of 598mln.

On 02/09 (weekend), i would except having the same figures but i only have USD (since it's hard-coded).

This missing during weekend decrease my month average utilisation. on 02/09, i expect having an average of 587mln while i  have 389mln (587 +191)/2.

PS : l would love sharing any template but i cant, my place doesnt allow me to share one drive to external addresses....

Thanks, O.

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Best: Check ultimate DAX pattern for currency conversion

Without model info:

you change Var _ccy = DISTINCT(ECB_Fixing[Currency]) to

Var _ccy = SELECTEDVALUE(ECB_Fixing[Currency])

Hope this help, kudos appreciated.

Proud to be a Super User!

Announcements