Forum Discussion
Date.AddDays not working with negative numbers
re:"You can see from the following table that when I use positive numbers I get the correct output, and when I use negative I just get today's date (CourseExpiry_Completion_Date1 and CourseExpiry_Completion_Date2):"
When I look at some of your dates in your screen shot:
and I put these data into a plain sheet and do the subtraction there I get:
That means the subtractions are correct.
So have a look at how the values in column ~.DaysUntilExpiry are calculated!
Hi Tayloramy, I've used the following code but I'm still getting today's date (sorry it's tiny):
You can see from the following table that when I use positive numbers I get the correct output, and when I use negative I just get today's date (CourseExpiry_Completion_Date1 and CourseExpiry_Completion_Date2):
Hi Kerria1276 ,
I just tested in a simple example, and it is working.
let
// Base rows to test with
Today = Date.From(DateTime.LocalNow()),
Source = #table(
{"Effective_End", "DaysOffset_Number", "DaysOffset_Text", "DaysOffset_UnicodeMinus"},
{
{ Today, -30, "-30", "−30" }, // negative
{ Today, -7, "-7", "−7" }, // negative
{ Today, 0, "0", "0" }, // zero
{ Today, 10, "10", "10" }, // positive
{ #date(2024, 2, 29), -365, "-365", "−365" }, // leap day case
{ null, -5, "-5", "−5" }, // null date
{ #date(2020, 1, 15), null, null, null }, // null offsets
{ #date(2020, 1, 15), 3, "bad", "bad" } // non-numeric text
}
),
Typed = Table.TransformColumnTypes(
Source,
{
{"Effective_End", type date},
{"DaysOffset_Number", Int64.Type},
{"DaysOffset_Text", type text},
{"DaysOffset_UnicodeMinus", type text}
}
),
// Intentionally broken: "as number" is a type assertion, not a converter
// This will error when the text isn't already a number type.
Broken_AsNumber =
Table.AddColumn(
Typed,
"Broken_AsNumber",
each Date.AddDays([Effective_End], [DaysOffset_Text] as number),
type date
),
// Correct: numeric offset directly
Add_From_Number =
Table.AddColumn(
Broken_AsNumber,
"Add_From_Number",
each if [Effective_End] <> null and [DaysOffset_Number] <> null
then Date.AddDays([Effective_End], [DaysOffset_Number])
else null,
type date
),
// Correct: convert text to number first
Add_From_Text =
Table.AddColumn(
Add_From_Number,
"Add_From_Text",
each
let o = try Number.From([DaysOffset_Text]) otherwise null
in if [Effective_End] <> null and o <> null
then Date.AddDays([Effective_End], o)
else null,
type date
),
// Correct: normalize Unicode minus (U+2212) to ASCII "-" before Number.From
Add_From_UnicodeMinus =
Table.AddColumn(
Add_From_Text,
"Add_From_UnicodeMinus",
each
let
raw = [DaysOffset_UnicodeMinus],
norm = if raw <> null then Text.Replace(raw, "−", "-") else null,
o = try Number.From(norm) otherwise null
in
if [Effective_End] <> null and o <> null
then Date.AddDays([Effective_End], o)
else null,
type date
)
in
Add_From_UnicodeMinus
This creates table with an effective_end date, an offset number, and an offset text field.
Then calculates the date.
Are you able to share more of your code to try and troubleshoot the problem?
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.
Hi tayloramy, thanks very much for your code. I copied and pasted it into a blank query in my Power BI file and it worked (as in those fields which were supposed to work, did, and the others brought back errors). So I can only think that this is to do with the way I have created the negative numbers (multiplying the positive numbers by -1 to create a new field, and then applying the same code as yours). I've made sure that there are no incorrect minus characters in there, but still no luck. I'll keep trying to find the answer!
Hi Kerria1276,
The way you make the numbers shoudln't matter as long as the data type is correct. Are you able to share your file?If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.