Need to get Creative to address Missing Values when Using Calculate Measure

[karabryan]

I have 4 tables.  Company, DateTable, Bookings Value, and Exchange Rate.  I have been having issues with null values causing issues when I try to sum the bookings value by date.  I've discovered the issue and am not sure how to solve it in the least painful way.

The issue is that the bookings value table has values for orders entered on weekends.  So the bookings date is on a weekend.  The exchange rates are only entered Mon-Fri.  Our ERP system pulls the Exchange rate from the date of the last entry for use on sales orders if there isn't a value for that date entered in the Exchange Rate table.  So, there isn't an issue on our sales orders in our ERP system but it does cause an issue with our Bookings calculations in Power BI.  Here is why.  We have an order entered in China on a Sunday.  They do business in RMB and the order is in RMB so the exchange rate on the order is in 1.000.  All is fine.  Our Mgmt team wants to see those values converted to USD, though, in PowerBI for the bookings report.  So, if they enter that Sales Order on a Sunday, and someone doesn't enter an exchange rate for RMB to USD in the Exchange Rate table over the weekend, there is no value for me to pull from in PowerBI.

There aren't null values in the tables I bring in from our ERP because all of the orders have dates and their appropriate exchange rate for that order and the Exchange Rate table is just a list of the dates values were entered.

So, there I can't look for null values in the original tables and replace them with something or fill down or some other type of solution.

I need to be able to look for times when there isn't an exchange rate for a date and have it fill in the exchnage rate from the previous date that there is a value.

Company                  DateTable                 BookingsOrderDate            ExchangeRateTable

A                                 1/1/2020                        1/1/2020                              1/1/2020      .01234

B                                 1/2/2020                        1/2/2020                               1/2/2020     .01570

C                                 1/3/2020                        1/3/2020                             

D                                 1/4/2020                         1/4/2020                              1/4/2020     .01350

                                    1/5/2020                        1/5/2020                               1/5/2020    .01273

                                    1/6/2020                        1/6/2020

                                    1/7/2020                        1/7/2020                                1/7/2020    .01421

I have a join between the company and the Bookings Table and the Exchange Rate Table

I have a join between the date table and the Bookings Table and the Exchange Rate Table

I use the exchange rate in the exchange rate table to calculate the new bookings value based on the Exchange Rate in the Exchange Rate table.  How can I write a formula that says, if we don't have a value in the exchange rate table while I am multiplying the Bookings Value by the Exchange Rate for that Bookings Order Date, use the value from the first previous date with a value for the exchange rate?  Is that possible?

[v-eqin-msft]

Hi @karabryan ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.

Best Regards,
Eyelyn Qin

[v-eqin-msft]

Hi @karabryan ,

According to my understand, you want to get the last and not blank rate ,right?

You could use Fill-->Down to replace blank values like this:

Then add a new column in BookingOrders table using LOOKUPVALUE() to find the matched rate:

Column =
LOOKUPVALUE (
    ExchangeRate[ExchangeRate - Copy],
    ExchangeRate[Date], 'BookingOrders'[BookingDate]
)

Or create a measure since there is a relationship in these two tables.

=
VAR LastNonBlankDate =
    CALCULATE (
        MAX ( 'ExchangeRate'[Date] ),
        FILTER (
            ALL ( ExchangeRate ),
            ExchangeRate[Date] <= MAX ( ExchangeRate[Date] )
                && ExchangeRate[ExchangeRate] <> BLANK ()
        )
    )
RETURN
    CALCULATE (
        MAX ( ExchangeRate[ExchangeRate] ),
        FILTER ( ALL ( ExchangeRate ), ExchangeRate[Date] = LastNonBlankDate )
    )

 My final output looks like this:

Here is the pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,
Eyelyn Qin