I’m going to show you how to deal with a currency rates table where some days are not accounted for. These days could be weekends or holidays.
This may not be a problem sometimes, especially if the business you’re preparing a report for does not open on weekends and holidays. But if they do, you might end up with incorrect totals at the end of the month.
Knowing how to deal with missing data will allow you to fairly compare sales if there are multiple currencies used. There may be days, for example, where you think that the total sales are decreasing because the value of the currency dipped. In reality, the total sales may be low because you’re not accounting for all the days in the given period.
In this example, I’m going to take the last available currency rate and use it on the missing dates to fill up the space. You may watch the full video of this tutorial at the bottom of this blog.
Given Data In The Currency Rates Table
Below is the fact table, which is the Currency Rates table. It uses the Euro as the base currency.
It shows data from January 2016 with the associated Crossrate on each day.
Everything looks good from January 1st. But when I hit the 26th, the next date on the succeeding row is the 29th.
This means that I’m missing data from the 27th and 28th.
Note that some might prefer to use Power Query to handle this situation. For now, I just want to focus on DAX.
Now let’s say I want to use 0.920 on the 27th and 28th. This is the Crossrate used on the 26th. How do I do that using DAX?
Some may say that this can be easily done in Excel. They may be right to some extent.
But the edge that Power BI has over Excel is the ability to further dive into the data from different perspectives without having to redo or change anything, which would most likely happen in Excel.
I’m going to show you all the measures you need to consider, as well as the different steps I would take to cover any missing data from the Currency Rates tables.
Currency Rate Measure
First, let me show you my main table. I already have some data here, including the Date and the Currency Rate for Euro.
In this case, the currency rate simply uses the SUM function.
Since I’m dealing with one currency rate per day, it’s easy to use functions like SUM, AVERAGE, MIN, or anything similar. The data showing up per row would make complete sense.
The only problem here is what’s going to happen to the Total.
This shows the Total as 26.693, which makes no sense at all. It’s literally just summing up all the days’ currency rates. Because of this, I would have to find a way to iterate over the days to get the real Total.
So I’ll look at my Currency Date measure to try and solve that.
Current Date Measure
Personally, I always make a Current Date measure that brings in whatever date I’m at in the Current Filter context.
I also prefer using MAX. In this example, using the MAX function will give me 1/31 at the Total, which would make more sense since that’s the last date given.
Last Currency Date
My table also contains the Last Currency Date. I get this by using the LASTDATE function and referencing the FactCurrencyRates and Date tables.
Although this shows the LASTDATE function, it’s also possible to use MAX. This uses the same premise as the Current Date table, but gives a specific point in time in relation to the Currency Rates table.
Scrolling down, I can see that there is no data for the 27th and 28th of the month.
To address those missing days, I’m going to talk about the Last Reported Currency Date column.
Last Reported Currency Date
The Last Reported Currency Date will drive what rate to use for the missing days. The concept is that the last reported currency rate will be the same rate to be used on the days with missing data.
Since there is no data on both the 27th and 28th, this means that the data for the 26th will be applied.
Our example also shows that the Last Reported Currency Date may not necessarily be the previous day. Since there is also no data for the 27th, I would still have to go back to the 26th to figure out what rate to use on the 28th.
Before I jump into the measure showing how this was done, remember that everything here is happening in the context of how a filter works. I have to think about what specific data I want to focus on.
Since I’m on the row for the 27th of the month, I want to remove any filter referencing and focus on the 26th instead. This means bringing the CALCULATE or CALCULATETABLE since those allow me to modify the filter applied. Don’t get that confused with the FILTER function, which can only restrict the data further.
So here’s the measure I used for the Last Reported Currency Date. I used CALCULATE for this example.
I always try to find as many variables as I can. I just need to look at the scope of each variable, since variables take on the value of where they’re defined.
In this case, I’m going to use the Current Date since I know that this variable is not going to change regardless of what functions I use.
Note that it isn’t required to find this variable. It’s just a habit that allows me to check the scope of every variable I’ll be using in the measure.
Going back to the measure, how do I tell DAX to remove the filters for the Current Date and give me the Last Recorded Currency Date?
First, I’ll use FILTER for the the ALL (DimDate) table.
The ALL function removes the filters applied, opening up the DimDate table for me to use.
Then, I use MIN to tell DAX that I want to use the part of the DimDate column that’s equal to either the Last Currency Date or the Current Date.
What if the Last Currency Date and the Current Date are not the same? Then always use the Last Currency Date.
This is why between 1/26 and 1/27, the measure will take 1/26. From here, the right date will be applied to the Currency Rates table to get the Last Reported Currency Rate.
Last Reported Currency Rate
Let me drag the Last Reported Currency Rate into my table.
You’ll see that the 27th and 28th are now using 0.9200 as the Currency Rate since it’s the same rate used on the 26th.
Below is the measure I used for the Last Reported Currency Rate.
Again, I always start off by defining some variables. In this case, I’m using Currency Selected and Last Reported Currency Date.
I’m going to use Currency Selected because I’m dealing with different currencies here. This is why I need to pinpoint what specific currency is being evaluated at any given time.
In this example, the different currencies can be accessed using the slicer given.
Of course, it doesn’t have to be a slicer that’s in use. It can also be coming from a table or a visual. What matters is that there’s a filter allowing you to see the data in different currencies.
As for the LastReportedDate, it’s the same thing as the Last Reported Currency Date that I talked about earlier.
For the Rate variable, I used the LOOKUPVALUE function.
I’m bringing in the Crossrate from the fact table for CurrencyRates.
Since I have a lot of currencies in a single table, I can’t just use the date as a single reference point. It has to be a combination of the right currency and the right date.
This is why I’m also referencing the currency ticker equals the Currency Selected.
I’m also referencing the LastReportedDate.
That’s why looking at the table, it shows the 26th three times before it jumps to the 29th.
Converted Sales Using Last Reported Rate Versus Using Current Date
I’m now on the final stretch, which involves converting the sales using the Last Reported Currency Rate.
Basically, the measure just takes this Last Reported Currency Rate and multiplies it by the Base Measure, which in this case is Total Sales.
Once that measure is applied, you’ll see that these two dates end up being blank. This means that there are no sales on these dates.
It also turns out that there are sales on the 27th and 28th (where there was previously no currency data).
Since I referenced the Last Reported Currency Rate, these numbers have been converted into Euro.
This time, I’m going to show you the Converted Sales Using the Current Date.
This time, it only looks at the Current Date instead of taking the Last Reported Date into consideration.
Once I put that measure in the table in another column, it only shows blank spaces on the 27th and 28th.
Comparing the Converted Sales between using the Last Report Rate versus using the Current Date, the Total shows a huge difference.
Where the Converted Sales Using the Last Reported Date shows a total of over 4 million, the Converted Sales Using the Current Date only shows 3.8 million in total.
This is why it’s very important to understand what variable you’re using. If you look at the Total, it only looks like the Euro took a big hit, pulling the value down. But in reality, the drop in Total is because of unaccounted data in the Currency Rates table.
Cleaning Up The Data
Now that I’ve filled in the missing data, I’m going to clean up the table.
I just added a Formatted Sales column here that clearly shows the numbers in Euros.
Since I went through so many steps, I’ve accumulated quite a number of columns here.
But not all of these are needed in presenting the data. So I’ll go ahead and get rid of those by removing them under the Values pane.
Once those have been removed, I end up with a cleaner table with only the necessary data showing.
Note that I can still change the currency by using the slicer here on the right.
To make my table more thorough, I’ll add all the other currencies by turning off the Single Select option.
This will display my numbers in USD, Euros, and British Pounds.
I’m also seeing some blank spaces here representing the days when there are no sales.
I’ll remove those by turning off “Show items with no data“.
Now, the table only contains rows representing the dates with sales.
This gives me a good comparison of the numbers based on the different currencies.
The Totals are also showing the correct numbers because I used SUMX to iterate through each of the rows.
This is very important. If you don’t use SUMX, it’s going to end up just adding all the columns without thinking about whether the right currency rate is being used.
***** Related Links *****
Creating A Currency Conversion Table In Power BI
Multiple Currency Logic In Power BI – A LOOKUPVALUE Example
Format Currencies In Power BI Using DAX
Conclusion
As I mentioned earlier, this is not necessarily the only way to deal with missing data. There are actually a ton of other ways to do this.
In some cases, for example, you could use the data from the start of the month and apply that same number throughout your entire Currency Rates table. I’ll be discussing those other approaches in the future in separate blogs.
For now, I used this approach because using the Last Reported Rate makes the most sense for me. I believe it’s the most intuitive option.
One thing I would also like to emphasize is the need to understand everything from a filter context. How do I overwrite the filter? How do I bring in the last reported data? These will help you understand how to get the right numbers in the end.
All the best,
Nick
