Forum Discussion
DATE RANGE LOOKUP
- Here it is, Create a Calculated Column
= CALCULATE(VALUES(Campaign[Campaign]),FILTER(Campaign,'Product'[Launch Date]<Campaign[Stop] && 'Product'[Launch Date]>Campaign[Start]))
= CALCULATE(VALUES(Campaign[Campaign]),FILTER(Campaign,'Product'[Launch Date]<Campaign[Stop] && 'Product'[Launch Date]>Campaign[Start]))
I'm trying to do something similar and for some reason i cannot use the column from the second table in the formula although they have a relationship that is active
Hi,
Share a dataset and show the expected result.
Hello Asish, All,
I am facing a similar problem but also failed to implent the suggested solution as I wasn't allows to use my second table in the FILTER formula.
Here my situation. In a nutstell I would like to look-up exchange rates from my table "Dated Conversion Rate" in order to transform my amounts in the Opportunity table into a standard currency (Euro in this case).
My experiemts got me to something like:
ER = CALCULATE(VALUES('Dated Conversion Rate'[ConversionRate],FILTER('Dated Conversion Rate','Dated Conversion Rate'[StartDate] < Opportunity[ClosedDate]...
but here PowerBI didn't let my CloseDate from the Opportunity get pulled into the equation.
These two tables do not have a relationship but looking at the original example this also wasn't the case there so hopefully this isn't the problem (if it is I am not sure what to do as I don't really have a nice key due to the nature of my date ranges in the conversion table).
Any help is hightly appreciated.
Sascha.
I think it just was a silly bracket issue. This formula appears to work just fine:
Exchange Rate = CALCULATE(VALUES('Dated Conversion Rate'[ConversionRate]),FILTER('Dated Conversion Rate','Dated Conversion Rate'[StartDate] <= Opportunity[CloseDate] && 'Dated Conversion Rate'[IsoCode] = Opportunity[CurrencyIsoCode] && 'Dated Conversion Rate'[NextStartDate] > Opportunity[CloseDate]))Sascha.
