Dax Exchange Rate

DAX DAX Calculations
1

Hi ,

I am seeking for a small explanation.

I wrote a formula to convert DKK to Euro.

Euro Revenue = SUMX(Charges,Charges[Gross Amount] * LOOKUPVALUE(‘Exchange Rate’[EUR],‘Exchange Rate’[Date],Charges[Invoice Date]))

If I understand correct Each individual row will be multiplied by the converted exchange rate.

0.1342 is the exchange rate for the below dates.

I am not 100% sure if the answer is correct or this is due that SUMX multiplies each line by the rate and that why the answer is slightly different.

1,062,168.8800 142,649.2800 = 0.1343

whereas 1,062,168.8800 * 0.1342 = 142,543.0637

image

2

Try using SUMX ( VALUES ( Charges[InvoiceDate] )…

1 Like
3

@Matty
I tackled the currency conversion issue in a series of videos. Hrere’s the first (and most basic) which I think would help

1 Like
4

@Nick_M are your files on youtube are avaiable to download on Enterprise?

5

Didnt work error in calculations

6

Can you please upload the file?

7

Unfortunately I can’t.

I used @Nick_M formula and I receive excatly the same result.

I think each row is multipled by conversation rate and then is sum it up, there will be always a roudning issue if we multiply each row by conversation versus if I would multiple total revenue and voncert to euro.

SUMX(Charges,Charges[Gross Amount] * LOOKUPVALUE(‘Exchange Rate’[EUR],‘Exchange Rate’[Date],Charges[Invoice Date]))

I can’t apply Values to Gross amount or to Invoice date since I am using lookupvalue

8

@Matty,

You can download the Enterprise DNA TV YouTube videos here:

https://forum.enterprisedna.co/c/enterprise-dna-online/7

Within that category I have a video on how to mask sensitive data so you can post your PBIX on the forum if you’d like to.

  • Brian
2 Likes
9

Thank you @BrianJ but what I meant is it possible do download Power BI report that Nick have used.

I will have look, thanks for that.

10

@Matty
Hard to see exactly the issue you are running into. A sample would be great. But if it’s dealing with total, somethign like this would work

Converted Sales, Using Current Date = 
SUMX(
    VALUES(DimDate)                                                 --whatever is on rows that you want the total for
    ,[Total Sales (Base Measure)] *
        LOOKUPVALUE(
            FactCurrencyRates[Crossrate]                            --CrossRate to bring back
            ,FactCurrencyRates[Ticker],[Currency Selected]          --using the currency selected, lookup that value in the fact table
            ,FactCurrencyRates[Date],[Current Date]                 --using the Last Currency Date date, lookup that value in the fact table
        )          
)
11

@Matty,

Thanks for the clarification. I would have no problem posting my PBIX file that accompanies each video, and I don’t think @Nick_M or @Melissa would either. I’ll talk with them and @sam.mckay about this. Appreciate the suggestion.

  • Brian
1 Like
12

Would be much appreciated when I watched mini series there are no resources :frowning:

13

@BrianJ @AntrikshSharma @Nick_M

Please find attachedExchange rate.pbix (74.1 KB)

14

@Matty-
Here’s what I see. Perhaps I’m not understanding the problem all the way

image
image927×961 57.8 KB

15

Yes we should be aiming to align relevant resources to all of the videos within miniseries tutorial modules. Will gather these altogether and upload them in the relevant places.

Sam

1 Like
16

Thanks @Nick_M all fixed now 1 decimal places was to short :slight_smile:

17

Hi Nick,

Enjoyed the video and I like your delivery style.

The query I have is around the use of [Current Date]. We use a daily conversion rate so I would want each Transaction line to pickup the Rate for the Date of the Transaction. If I used the method in the video and the Context was Monthly or even Yearly it would give me the Rate for the Last Day of the Month or Year which in my Scenario would be incorrect as there may be 30 or 31 different Rates depending on the month or 365 if it was Year.

I need to iterate through the Table but I am struggling to see how. Any advice greatfully received.

Thanks

Chris

18

@chris.turnbull
Yes, in the first video I didnt account for the overall total as that video’s goal was to just start the whole idea of getting the correct currency date. If you take a a look at this video ( around the 14 min mark) I show how to get the correct totals where the total is the sum of all individual dates * that specific currency rate and not just taking the total * the last reported date.

Let me know if that wasnt want you were looking for or if doesnt quite answer the question