Forum Discussion

MiKeZZa's avatar
MiKeZZa
Icon for Post Patron rankPost Patron
5 years ago
Solved

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

Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

6 Replies

    • MiKeZZa's avatar
      MiKeZZa
      Icon for Post Patron rankPost Patron
      5 years ago

      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's avatar
        MFelix
        Icon for Super User rankSuper User
        5 years ago

        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's avatar
    MiKeZZa
    Icon for Post Patron rankPost Patron
    5 years ago

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