Forum Discussion

JimJim's avatar
JimJim
Icon for Responsive Resident rankResponsive Resident
5 years ago
Solved

Lookup value with multiple filters

Hi Team,
I have two tables, Sales and Currency. See below:


I would like to populate Sales.Exchange Rate GBP and Sales.Exchange Rate USD with the exchange rates from the currency table (values above are my expected results)
The logic I need to use looks something like this (For GBP):

select earliest exchange rate from Currency where Sales.Currency = Currency.Source and Currency.Target = 'GBP' and Sales.Date > Currency.Date


How can I accomplish this?

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    https://www.dropbox.com/s/o04lz90nbzttotv/reporting%20model.pbix?dl=0 

     

    Exchange rate GBP CC=
    VAR sourcecurrency = Requests[Currency]
    VAR targetcurrency = "GBP"
    VAR currentdate = Requests[Date Requested]
    VAR maxcurrencydate =
    CALCULATE (
    MAX ( 'Currency'[date] ),
    FILTER (
    'Currency',
    'Currency'[date] <= currentdate
    && 'Currency'[sourcecurrency] = sourcecurrency
    && 'Currency'[targetcurrency] = targetcurrency
    )
    )
    RETURN
    CALCULATE (
    CALCULATE ( SUM ( 'Currency'[exchangerate] ) ),
    FILTER (
    'Currency',
    'Currency'[date] = maxcurrencydate
    && 'Currency'[sourcecurrency] = sourcecurrency
    && 'Currency'[targetcurrency] = targetcurrency
    )
    )

     

     

7 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

     

     

    Qty total : =
    SUM(Sales[Qty])
     
    Currency : =
    IF( ISFILTERED(Dates[Date]),
    SELECTEDVALUE(Sales[Currency]))
     
    Exchange rate GBP =
    VAR _sourcecurrency =
    MAX ( SourceCurrency[Source] )
    VAR _currentdate =
    MAX ( Dates[Date] )
    RETURN
    IF (
    NOT ISBLANK ( [Qty total :] )&& ISFILTERED(Dates[Date]),
    CALCULATE (
    LASTNONBLANKVALUE (
    Dates[Date],
    CALCULATE (
    SUM ( 'Currency'[Rate] ),
    FILTER (
    'Currency',
    'Currency'[Source] = _sourcecurrency
    && 'Currency'[Target] = "GBP"
    )
    )
    ),
    FILTER ( ALL ( Dates ), Dates[Date] <= _currentdate )
    )
    )
     
    Exchange rate USD =
    VAR _sourcecurrency =
    MAX ( SourceCurrency[Source] )
    VAR _currentdate =
    MAX ( Dates[Date] )
    RETURN
    IF (
    NOT ISBLANK ( [Qty total :] ) && ISFILTERED(Dates[Date]),
    CALCULATE (
    LASTNONBLANKVALUE (
    Dates[Date],
    CALCULATE (
    SUM ( 'Currency'[Rate] ),
    FILTER (
    'Currency',
    'Currency'[Source] = _sourcecurrency
    && 'Currency'[Target] = "USD"
    )
    )
    ),
    FILTER ( ALL ( Dates ), Dates[Date] <= _currentdate )
    )
    )
     
     
     
    • JimJim's avatar
      JimJim
      Icon for Responsive Resident rankResponsive Resident
      5 years ago

      I see you have created a SourceCurrency table, is this a necessary step in addition to my current currency table? My source currencies have more than just AUD so ideally I would rather not have to create this

      J

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        5 years ago

        You are right.
        If you do not have other currencies, it is not mandatorily needed.

  • JimJim's avatar
    JimJim
    Icon for Responsive Resident rankResponsive Resident
    5 years ago

    Thank you Jihwan_Kim ,

    I appreciate the time you spent doing this, I will try and make it work