Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MiKeZZa
Post Patron
Post Patron

Currency conversion on choosen reporting date

Hi all,

 

I'm looking for a solution for my DAX issue. I have a star schema with a facttable which contains amounts in several currencies. I want to pick a date in the report and then the amounts must be divided by the rate as known on the choosed reporting date. So far it's not that complex I guess. I have a regular star schema.

 

I have a special measure for calculating the correct value in the currency where it is saved:

 

VAR MaxDate = MAX(ReportingDate[Date])

RETURN CALCULATE([AmountSum], TransactionType[TransactionType]="Type A", ALL(ReportingDate), 'Product'[Approval Date] <= MaxDate)

 

 

This works good, but I'm totally clueless about how to determine the correct rate and divide the outcome of this measure by that rate. I was able to create a table with the rates as choosen on the max of the selected date(s).  I've done that with (by example):

 

VAR MaxDate = MAX(ReportingDate[Id])

VAR RatesOnReportingDate = FILTER(Rates, [ReportingDateId] = MaxDate)

 


That gives me a table with the rate per currency on the max reportingdate.

 

I've tried things, many things, like naturalinnerjoin and so on. But I can't get it working.

 

Is there somebody who can tell me what to do to get this working? I think it's not that hard and I'm close...

 

PBIX with example; https://file.io/avULGIHkWmpJ

1 ACCEPTED SOLUTION

Hi @MiKeZZa ,

 

First of all you need to have all you ReportingDateID as Whole numbers

 

Try the following measure, if this is not the correct result please tell me so I can revise:

Sales Currency = 
    VAR SelectedCurrency =
        SELECTEDVALUE ( 'Currency'[Code] )
    VAR DatesExchange =
        SUMMARIZE (
            Rates,
            ReportingDate[Month year],
            Rates[Rate]
        )
    VAR Result =
                SUMX (
                    DatesExchange,
                    DIVIDE([AmountSum] , Rates[Rate])
                )
       
       
    RETURN
        Result

 

PBIX file attach.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

6 REPLIES 6
MiKeZZa
Post Patron
Post Patron

Really really great @MFelix! Thanks a lot.

 

For my understanding; is DatesExchange a temporary table based on Rates, but does it get a place in the model with the relations as defined for Rates? Or what's the trick here? How can I make a table with Rates and use it in the divide function and be sure that it gets the right currency (which it does actually)?

MFelix
Super User
Super User

Hi @MiKeZZa ,

 

The file is not available says it was deleted.

 

But you need to have a calculation made with the same pattern has SQLBI (https://www.sqlbi.com/articles/currency-conversion-in-power-bi-reports/)

 

If you can share the file I can setup the measure accordingly.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

I tried to get it working with the SQLBI method, but couldn't get it working. Be aware that I can't change many things on the existing model.

 

Editing the OP is giving HTML errors, so here is a new link; download. Does this work?

Hi @MiKeZZa ,

 

First of all you need to have all you ReportingDateID as Whole numbers

 

Try the following measure, if this is not the correct result please tell me so I can revise:

Sales Currency = 
    VAR SelectedCurrency =
        SELECTEDVALUE ( 'Currency'[Code] )
    VAR DatesExchange =
        SUMMARIZE (
            Rates,
            ReportingDate[Month year],
            Rates[Rate]
        )
    VAR Result =
                SUMX (
                    DatesExchange,
                    DIVIDE([AmountSum] , Rates[Rate])
                )
       
       
    RETURN
        Result

 

PBIX file attach.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix,

 

That's really close to what I want!

 

I did change the measure in the Divide and that's great for every line. Except the total line. I've tried some things, but that total line is giving me some issues. I've had a way to determine if I'm in a detailline or the totalline, with ISFILTERED. But I don't know what to do in totalline to get what I want.

 

You also have a clue for that?

 

I've attacted a new PBIX, with better rates and larger numbers. As you can see the total is not the sum of the lines, but the sum of all the values and then divided by a (I don't know which one) rate.

 

PBIX can be found here

Hi @MiKeZZa ,

 

This has to do with context transiction the fastes way is to create a new measure with the following syntax:

Total values = SUMX(VALUES('Currency'[Code]), [Sales Currency])

 

MFelix_0-1619512118986.png

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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