cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ScR Frequent Visitor
Frequent Visitor

Many to one currency conversion

I am in need of some guidance.


I am looking to creating a Many-to-One conversion for our sales reports but I can´t seem to get it right. I am way out of my depth here but I would love to get some help in how I can create a measure to convert the total to a base currency.


I basically just want to report a single currency and have the conversion be based on different exchange rates throughout the reporting period.

 

Screenshot BI.PNGfairly accurate represenation of how the production files looks like in regards to the currency.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thanks in advance for any pointers.

 

External - .pbi file

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
kschaefers Regular Visitor
Regular Visitor

Re: Many to one currency conversion

This, although it may seem simple is a considerably more difficult task.

 

You can use this formula:

 

Total (Base Currency) =
 (
    SUMX (
        Sales,
        VAR current_date = Sales[Date]
        RETURN
            Sales[Amount]
                * CALCULATE (
                    CALCULATE (
                        VALUES ( dimExchange[Exchange Rate] ),
                        LASTDATE ( dimExchange[Date Key] )
                    ),
                    FILTER ( ALL ( dimCalendar ), dimCalendar[Date Key] <= current_date )
                )
    )
)

As you can see this is significantly more difficult than the other solution 🙂

The code basically iterates through each row of the Sales table, transforms the Row Context into a filter context (to filter the Currency on the DimExchange Table) and then modifies the dimCalendar filter to select all dates prior to the current date (in the iterated Sales table row). Finally it chooses the last date available for the given Filter set from the DimExchange table.

 

It took me a while to wrap my head around the whole Filter and Row Context topic as well as context transition, and I think I am just scratching the surface, so maybe there is a more elegant solution here 🙂

View solution in original post

4 REPLIES 4
kschaefers Regular Visitor
Regular Visitor

Re: Many to one currency conversion

Hi @ScR,

 

simplest solution would be to use:

Total (Base Currency) = SUMX(dimExchange,[Total]*dimExchange[Exchange Rate])

 You get the following:image.pngAs you can see there are no exchange rates for most of the days, but I assume this is beacuse it is sample data

ScR Frequent Visitor
Frequent Visitor

Re: Many to one currency conversion

Thanks @kschaefers.

 

Yes and no on the lack of data. Is it possible to have all the sales data between the available exchange rate dates use the previous data point for conversion? Am I going about this the wrong way?

 

The problem is that there will probably not be exchange rate data for all the dates.

 

 

Highlighted
kschaefers Regular Visitor
Regular Visitor

Re: Many to one currency conversion

This, although it may seem simple is a considerably more difficult task.

 

You can use this formula:

 

Total (Base Currency) =
 (
    SUMX (
        Sales,
        VAR current_date = Sales[Date]
        RETURN
            Sales[Amount]
                * CALCULATE (
                    CALCULATE (
                        VALUES ( dimExchange[Exchange Rate] ),
                        LASTDATE ( dimExchange[Date Key] )
                    ),
                    FILTER ( ALL ( dimCalendar ), dimCalendar[Date Key] <= current_date )
                )
    )
)

As you can see this is significantly more difficult than the other solution 🙂

The code basically iterates through each row of the Sales table, transforms the Row Context into a filter context (to filter the Currency on the DimExchange Table) and then modifies the dimCalendar filter to select all dates prior to the current date (in the iterated Sales table row). Finally it chooses the last date available for the given Filter set from the DimExchange table.

 

It took me a while to wrap my head around the whole Filter and Row Context topic as well as context transition, and I think I am just scratching the surface, so maybe there is a more elegant solution here 🙂

View solution in original post

ScR Frequent Visitor
Frequent Visitor

Re: Many to one currency conversion

I have to dive in to that and see if I understand it correctly.

 

Thanks a lot for your time @kschaefers

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 282 members 2,851 guests
Please welcome our newest community members: