Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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:
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
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))
@Anonymous , Not very clear . but you have use a filter like in measure or calculatetable
filter(ExchangeRate , ExchangeRate[Date] = min(DimCalendar[Date]))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
101 | |
94 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |