Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
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.
Hi,
Share the download link of the PBI file.
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.
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |