Duration.Days(DateTime.LocalNow()-[Date]) giving different results for same date range

Good day KCarlton96

DateTime.LocalNow() will return a datetime. Your [Date] column must also be a datetime, otherwise you would get an error message when trying to subtract a date type from a datetime type. To created a consistent result convert DateTime.LocalNow() to a date and convert [Date] to a date before calculating the duration. Here is an example reproducing the problem (the "Inconsistent" column) and showing the remedy (the "Consistent" column).

let
  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTQN9M3MjAyUTAysDIwUIrVQRYzNAOLxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
  #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}}),
  #"Added Consistent" = Table.AddColumn(#"Changed Type", "Inconsistent", each Duration.Days(DateTime.LocalNow()-[Date]), Int64.Type),
  #"Added Inconsistent" = Table.AddColumn(#"Added Consistent", "Consistent", each Duration.Days( DateTime.Date(DateTime.LocalNow()) - DateTime.Date([Date]) ), Int64.Type)
in
  #"Added Inconsistent"

This produces,

The inconsistency arises because, let's say it is 16:00 hours when you refresh and your date column contains data all of the same date but some times before 16:00 and some after. For those before 16:00 the duration will be one day more than those after 16:00.

Hope this helps