LASTNONBLANK issue

Drikus · ‎09-27-2023

I have a listing of sales invoices which I need to translate the value to USD using the rate on the date of the invoice.

I have set up an FX table that automatically imports the FX rates from the European Central bank to create a table for FX rates.

There are no exchange rates released for certain days (weekends, public holidays etc).

I have created a calculated column to calculate the date of the latest available date using LASTNONBLANK.

I have also created a column to see how many days are between my calculated FX date and the Invoice date.

I would only expect to see between 0-4 days variance between the two, but I'm seeing large numbers.

For 2021 its working well till it gets to March and is then stuck on 31 March 2021 till the end of the year.

For 2022 its the same story it works well till the 30th of September and is then stuck on that date till the end of the year.

What could I be doing wrong in my calculation for the Latest available date?

My issue can easily be recreated.

I have 3 Tables

d_Date which is my date table and marked as a date table.
ECB_FXRate which is the exchange rates per the European Central bank. MCode code down below.

Invoice listing (TEST DATE). Which I've just recreated as being each day in the year to test the formula

Date table below (Calculated Table):

d_Date = 
ADDCOLUMNS (
CALENDAR(DATE(2021,01,01),Date(2023,12,01)),
    "DateKey", VALUE ( FORMAT ( [Date], "YYYYMMDD" ) ),
    "Year", YEAR ( [Date] ),
    "Quarter Number", INT ( FORMAT ( [Date], "q" ) ),
    "Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Week Day Number", WEEKDAY ( [Date] ),
    "Week Day", FORMAT ( [Date], "dddd" ),
    "Year Month Number", YEAR ( [Date] ) * 100 + MONTH ( [Date] ),
    "Year Month", FORMAT ( [Date], "mmmm" ) & " " & YEAR ( [Date] ),
    "Year Quarter Number", YEAR ( [Date] ) * 100 + INT ( FORMAT ( [Date], "q" ) ),
    "Year Quarter", "Q" & FORMAT ( [Date], "q" ) & "-" & YEAR ( [Date] ),
    "Day",DAY([Date]),
    "Month End",EOMONTH([Date],0)
)

ECB_FXRates - MCode to create FX Rate Table:

let
  Source = Xml.Tables(
    Web.Contents(
      "https://www.ecb.europa.eu/stats/eurofxref/eurofxref-sdmx.xml?11c21b73004dd2d4795959d4cfff466b"
    )
  ), 
  Table1 = Source{1}[Table], 
  Table0 = Table1{0}[Table], 
  Table2 = Table0{1}[Table], 
  #"Changed Type" = Table.TransformColumnTypes(
    Table2, 
    {
      {"Attribute:FREQ", type text}, 
      {"Attribute:CURRENCY", type text}, 
      {"Attribute:CURRENCY_DENOM", type text}, 
      {"Attribute:EXR_TYPE", type text}, 
      {"Attribute:EXR_SUFFIX", type text}, 
      {"Attribute:TIME_FORMAT", type duration}, 
      {"Attribute:COLLECTION", type text}
    }
  ), 
  #"Removed Columns" = Table.RemoveColumns(
    #"Changed Type", 
    {
      "Attribute:FREQ", 
      "Attribute:CURRENCY_DENOM", 
      "Attribute:EXR_TYPE", 
      "Attribute:EXR_SUFFIX", 
      "Attribute:TIME_FORMAT", 
      "Attribute:COLLECTION"
    }
  ), 
  #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([#"Attribute:CURRENCY"] = "CAD" or [#"Attribute:CURRENCY"] = "CNY" or [#"Attribute:CURRENCY"] = "GBP" or [#"Attribute:CURRENCY"] = "MXN" or [#"Attribute:CURRENCY"] = "TRY" or [#"Attribute:CURRENCY"] = "USD")),
    #"Expanded Obs" = Table.ExpandTableColumn(
    #"Filtered Rows", 
    "Obs", 
    {"Attribute:TIME_PERIOD", "Attribute:OBS_VALUE"}, 
    {"Attribute:TIME_PERIOD", "Attribute:OBS_VALUE"}
  ), 
  #"Changed Type1" = Table.TransformColumnTypes(
    #"Expanded Obs", 
    {{"Attribute:TIME_PERIOD", type date}, {"Attribute:OBS_VALUE", type number}}
  ), 
  #"Renamed Columns" = Table.RenameColumns(
    #"Changed Type1", 
    {
      {"Attribute:TIME_PERIOD", "Date"}, 
      {"Attribute:OBS_VALUE", "Value"}, 
      {"Attribute:CURRENCY", "Attribute"}
    }
  ),
    DateFilter = Table.SelectRows(#"Renamed Columns", each [Date] > #date(2020, 03, 01)),
    #"Add Euro" = Table.AddColumn(
    Table.AddColumn(
      Table.Distinct(Table.SelectColumns(DateFilter, "Date")), 
      "Attribute", 
      each "EUR", 
      type text
    ), 
    "Value", 
    each 1, 
    type number
  ), 
  #"Appended Query" = Table.Combine({#"Add Euro", DateFilter}), 
  #"Sorted Rows" = Table.Sort(#"Appended Query", {{"Date", Order.Ascending}}),
    AddUSDasBase = Table.AddColumn(#"Sorted Rows", "BaseRate", each "USD", type text),
    BufferedTable = Table.Buffer(AddUSDasBase),
    #"Merged Queries" = Table.NestedJoin(BufferedTable, {"Date", "BaseRate"}, AddUSDasBase, {"Date", "Attribute"}, "FX Table", JoinKind.LeftOuter),
    #"Expanded FX Rates" = Table.ExpandTableColumn(#"Merged Queries", "FX Table", {"Value"}, {"ToBaseRate"}),
    #"Added Custom" = Table.AddColumn(#"Expanded FX Rates", "USDRate", each Value.Divide([Value],[ToBaseRate]), type number),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Date", "Attribute", "USDRate"}),
    FXTable = Table.RenameColumns(#"Removed Other Columns",{{"Attribute", "Currency"}})
in
    FXTable

Invoice Listing (Calculated table):

TEST DATE =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 01, 01 ), DATE ( 2023, 12, 01 ) ),
    "Week Day Number", WEEKDAY ( [Date] ),
    "Week Day", FORMAT ( [Date], "dddd" )
)

Calculated column formula in Invoice listing

FX_Date = 
VAR DDate = 'TEST DATE'[InvoiceDate]

VAR DateTable =
    FILTER ( 'd_Date', 'd_Date'[Date] <= DDate)

VAR Result =
CALCULATE (
VALUES('d_Date'[Date] ),
LASTNONBLANK ( DateTable, COUNTROWS ( RELATEDTABLE ( ECB_FXRates ) ) )
)

RETURN
Result

Drikus · ‎09-28-2023

@some_bih Thanks so much. I'll definitely work through that article. That might just be a better way than I'm doing at the moment.

Yes the dates was a typo 🙂

I'm still quite curious why LASTNONBLANK is not returning the correct value.

For 2021 its working well up until 31 March 2021 and for 2022 its working well up until 30 September 2022.
Its strange to see a formula performing as expected for a certain portion of the year, but not for the other portion of the year