Using the most recent data when actual data not available

[mterry]

I have a report with two separate datasets, one is costs reported in local currency, the other is officially accepted conversion rates set internally by my company. The issue I'm encountering is that the report needs to be accessed throughout any given month, and the official currency rates are not released until the end of the month. I have historically been merging the FX rates onto the cost table and only refreshing/reporting for the previous month after rates are released, but this is becoming a bit of a problem as people are wanting more up-to-date data. An acceptable solution would be to use the last months conversion rate until the official rates are released, but I'm not certain what the best/most efficient way to accomplish this would be? Would the easiest way to accomplish this be to create a new table that copies last months rates and applies them to the current  month, then merge that separately to the cost table in a second step? That's what I had in mind, but before trying that I didn't know if there was perhpas a more streamlined way to do this in one step when the historic rates are merged, or perhaps after they're merged? Any insight would be appreciated. 

[Daniel29195]

@mterry 

if you do what you are saying, then what will happen when the rates of the month are released ? should you always fix manually your data or is it done automated ? 

Since you have 1 currency that you want to convert to. 

i would suggest maybe to create  a calculated column in the fact_table with the cost converted to the currency wanted . 

in dax,  you get the last available exchangerate at condition that the date of exchangerate is <= fact_transaction_date . you put it in a variable .

then you multiply or divide ( base on the conversion formula )  the cost  by the variable you have created above .

this way, when data the new exchangerate for the month is populated, it automatically changes the new calcualted column to take into consideration the latest exchange rate values . 

i did it in a project i was working on, and i have encountered similar thing . 

let me know if it works out for you . 

If my response has successfully addressed your issue kindly consider marking it as the accepted solution! This will help others find it quickly. Dont forget to hit that  thumbs up button 🫡👍

[mterry]

I'm working to use a trigger to refresh the data, so when a new item is added to a sharepoint list it will trigger a refresh. When the new rates are available the FX table that I use as a data source will be refreshed and those rates will be used - so there will have to be some logic built in to whatever solutions I use. I think I understand what you're saying so I'll look into that solution. Thanks