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

     

     

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.

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        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
        )
        )