Filter on Sliced Date

[swingr45]

Hey guys! I'm just starting out with PowerPivot and I've hit a roadblock. I have a Date Table set up that I use to filter my data. I also have linked a table of daily exchange rates, but it is not completely filled in daily (e.g. I have rates for 5/24/14 and 5/26/14, but not 5/25/14...) I'm trying to make a calculated field so that when I slice on the complete Date Table, the most recent exchange rate to the filtered date is used to multiply another field "Amount" (e.g. - if I filter on 5/25/14, I would like the "Amount" field to be multiplied by the exchange rate from 5/24/14 since the 5/25/14 rate isn't available. If I filtered on 5/26/14, it would multiply by the 5/26/14 rate...) Can somebody please help me out or point me in the right direction? I think it will require a LASTDATE() function, but haven't been able to work it out yet and I'm starting to get frustrated. Thanks!

 

[scottsen]

How about something like this?

Best Date:=CALCULATE(LASTDATE(Fx[Date]), FILTER(ALL(Calendar), Calendar[Date] <= MAX(Calendar[Date])))
FxRate := LOOKUPVALUE(FX[Rate], FX[Date], [Best Date])

 

[swingr45]

Great! Thank you! The date calculation works beautifully, but so far I'm unable to get the rate calculation to work because I need to take it slightly further than your code currently allows.

I have multiple tables with entries from different currencies. Each entry has the currency that it is in. I also have a linked table with all of the different currencies listed out. I'm using this table between the entries table and the exchange rate table.

My thought is now take the date calculation that you have provided me and use that like you showed for the rate, but also look at the entry's currency, as their will be multiple exchange rates each day, but only one for each currency. Could you help me out a little further? I'm able to hard code a Currency Code, but I'd like to be able to get it from each entry itself...

Maybe I'm making it harder than it should be

USDRate := LOOKUPVALUE(ExchRates[Relational Exch_ Rate Amount],ExchRates[Starting Date],[Date],ExchRates[Currency Code],"MYR")

 

[Tianbas]

I had exactly the same issue to solve some month ago. Here the approach I have used:

From our system I had a table with daily exchanges rates but with no rows for weekends and holidays.
I created a second daily_rate table with all dates for all currencies that we use in Excel (two columns only date and currency code) and made it a linked table in PowerPivot.
Now I used RELATED to get the exchange rates from the first table in the second. It still has no rates for weekends but at least a row.
The date of last available rate I calculated with

Date_of_last_Actual = if(daily_rate[Fx_from_sytem]>0,"",calculate(max(daily_rate[Key_Date]),filter(daily_rate,daily_rate[Currency]=earlier(daily_rate[Currency])&&daily_rate[Key_Date]<earlier(daily_rate[Key_Date]))))

With that I could add a calculated column that fills the gaps and has an exchange rate for every date

=if(daily_rate[Fx_from_sytem]>0,daily_rate[Fx_from_system],calculate(average(daily_rate[Fx_from_system]),filter(daily_rate,earlier(daily_rate[Date_of_last_Actual])=daily_rate[Key_Date]&& daily_rate[Currency]=earlier(daily_rate[Currency])))))


My formula does not look as good as Scott's formula but I can't use LOOKUPVALUE with my PowerPivot version.

 

[swingr45]

Thanks for you input Tianbas. I actually realized my mistake and have fixed it so I'm sticking with Scott's answer. Thank you guys for your help!