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

[KCarlton96]

Good day, 

As it says in my title, I am currently using the below to calculate the days between two dates but it is returning different values for different rows.

Date is 5-JUN-2024

DateTime.LocalNow() is 17-JUN-2024

Duration.Days(DateTime.LocalNow()-[Date])

 The correct count of days is 11, but as shown below, I am getting both 11 and 12 returned for the date range.

Is there any way to fix this?

[collinsg · Accepted solution]

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

[KCarlton96]

Thank you so much, this ended up working for me.

[tharunkumarRTK]

@KCarlton96 

What is the data type of your date column, I hope it Date and not DateTime. Can you share the pbix file with sample data?

Need Power BI consultation, hire me on UpWork .

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun