Currency conversion on choosen reporting date

[MiKeZZa]

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

[MiKeZZa]

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]

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.

[MiKeZZa]

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?

[MFelix · 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.

[MiKeZZa]

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

[MFelix]

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])