Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Moody__01
Helper I
Helper I

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 Moody__01_0-1696003567681.png

 

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]

 

Moody__01_1-1696003910825.png

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
Moody__01
Helper I
Helper I

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.

Moody__01_0-1696259509184.png

 

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

Moody__01_1-1696259621951.png

 

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.

Moody__01_2-1696259765490.png

 

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

 

 

Thanks, O.

 

 

 

 

Ashish_Mathur
Super User
Super User

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
some_bih
Super User
Super User

Hi @Moody__01 

Best: Check ultimate DAX pattern for currency conversion

https://www.daxpatterns.com/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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.