Get unaggregated value of a table with DAX measure

[Anonymous]

Hello everyone,

I am working with a historic currency table which has the following columns:
    Currency, ExchangeRate, ExchangeRateDate. 

I am trying to get a DAX measure which would give me the oldest currency exchange rate for each currency. When I say the oldest, I mean the oldest value that is defined by my date slicer in the report.  This date can be modified by the user via the slicer, therefore I cannot use a calculated column or table but I need to do it in a DAX measure. 

For example, if my slicer is set between 2018/12/31 and 2019/12/31, I would like to get the exchange rates on the 2018/12/31.  Below is the result I would like to obtain (it should be in a measure which will be used in another measure afterwards).

Currency	ExchangeRate	Date
GBP	0.66	31/12/2018
INR	0.77	31/12/2018
USD	0.88	31/12/2018

To give a more concrete situation, I need to add a column with this value to this table:

I tried using the CALCULATE function, but it requires me to use an aggregation function, which I do not need, and when I use eg. MAX it returns the highest exchange rate accross the different currencies. 

The relation model between my dimensions and facts are:

• DimCalendar[Date] -> ExchangeRate[Date] (1 to many)
• DimCurrency[Currency] -> DimSecurity[Currency] (1 to many)
• DimCurrency[Currency] -> ExchangeRate[Currency] (1 to many)

I find it weird that I cannot "simply" do the equivalent of the follow SQL query in DAX 

select *

from ExchangeRate as er

join DimCalendar as dc on er.Date = dc.Date

where er.Date = min(dc.Date)

;

I am pretty sure my problem is context related but I can't quite get it.

Any help would be terrific. Please let me know if I did not provide enough information.

Best regards,

Thomas

[v-xuding-msft]

Hi @Anonymous ,

Please try this:

Measure = 
var mindate = MIN(DimCalendar[Date])
return
CALCULATE(MAX(ExchangeRate[ExchangeRate]),FILTER(ExchangeRate,ExchangeRate[Date] = mindate))

It will show maximum value by default. If you want to show total values, please try this:

Measure 2 = 
var mindate = MIN(DimCalendar[Date])
return
CALCULATE(SUM(ExchangeRate[ExchangeRate]),FILTER(ExchangeRate,ExchangeRate[Date] = mindate))

[amitchandak]

@Anonymous , Not very clear . but you have use a filter like in measure or calculatetable

filter(ExchangeRate , ExchangeRate[Date] = min(DimCalendar[Date]))