Currency conversion-hierarchy date filter

[Suryaann111]

Hi All,

I need some help in the logic.I have written a DAX query to calculate the exchange rate.

CALCULATE(MIN(T_CURRENCY[EXCHANGE_RATE]),FILTER(FILTER(T_CURRENCY,AND(AND(T_CURRENCY[FROM_CURRENCY]=MIN(T_SALES[FROM_CURRENCY]),T_CURRENCY[TO_CURRENCY]=FIRSTNONBLANK[T_CURRENCY(TO_CURRENCY],1)),

MIN(T_CALENDER[CALENDER_DATE]<=T_CURRENCY[EFF_END_DATE])),MIN(T_CALENDER[CALENDER_DATE]>=T_CURRENCY[EFF_START_DATE]);

T_CURRENCY TABLE

currency_ID From currency To_currency exchange rate EFF_START_DATE EFF_END_DATE
12 eur nok 4 6/1/2018 6/30/2018
12 eur nok 4 5/1/2018 5/31/2018
12 eur sek 6 6/1/2018 6/30/2018

slicers in the report:

to currency

calender date - hierarchy slicer.

The above DAX code will retrieve correct exchange rate only when a day is seleted in the date hierarchy slicer.

The problem here is when we select a year,say 2018,then the exchange rate should be picked from the latest month of 2018 available in the T_CURRENCY table.

if the to currency selected is nok ,and year selected is 2018,then exchange rate should be 4 (latest month of 2018 is 6).

Can someone help to incorporate this date logic in the above DAX.

Thanks in advance.

[v-lid-msft]

Hi @Suryaann111 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft · Accepted solution]

Hi @Suryaann111 ,

We can try to use the following measure to meet your requirement:

Latest Exchange Rate =
CALCULATE (
    MIN ( T_CURRENCY[EXCHANGE_RATE] ),
    FILTER (
        ALLSELECTED ( T_CURRENCY ),
        T_CURRENCY[EFF_END_DATE] = CALCULATE ( MAX ( T_CURRENCY[EFF_END_DATE] ) )
    )
)

Best regards,