Date.AddDays not working with negative numbers

[Kerria1276]

My formula works when my second argument is a positive number - but just brings back today's date when it's negative:

= Table.AddColumn(#"Changed Type3", "CourseExpiry_Completion_Date", each Date.AddDays([Effective_End],[DaysUntilExpiry_Negative] as number))

Any advice would be appreciated, thank you.

[v-sdhruv]

Hi @Kerria1276 ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out.
Thank You Super Users for your prompt response to the query.

[m_dekorte]

Hi @Kerria1276,

@tayloramyprovided excellent support - thank you for that.

Here’s my two cents: Is LD_CourseExpiry_Odata.Days Until Expiry calculated at the source system you’re connecting to? It seems like that value is tied to the current date, especially when Effective_End is greater than today. If you were expecting something different, could you share a bit more about the outcome you’re looking for?

[Kerria1276]

Hi @m_dekorte . LD_CourseExpiry_Odata.Days Until Expiry is calculated at the source system. In this dataset, I have the Effective End date of the course and Days Until Expiry, but not the Course Completion Date (in some cases), hence the need to calculate it. So I then inverted the Days Until Expiry figure by multiplying by -1 to get the negative value. I then did an Add.Days formula to find the original course completion date. I have had to do a lot of work to get the dates in the right format before doing the calculation, as they feed down incorrectly from the source system (eg 3/9/2024 instead of 03/09/2024) and I don't know if this has something to do with it, as my code seems to be ok. Thanks for your input.

[p45cal]

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!

[m_dekorte]

To me, it seems that the original course completion date can’t be derived this way. You could look for other fields in your dataset or bring in additional ones that provide the information you need.

As for the date format issue (e.g., 3/9/2024 instead of 03/09/2024), you can use the optional third parameter, Culture, in your Changed Type step. This lets the M engine know which format to expect, for example, specifying "en-US" for U.S. date formats.

Table.TransformColumnTypes(
    PreviousStepName,
    {{"Effective_End", type date}},
    "en-US"
)

Illustrated here.

[v-sdhruv]

Hi @Kerria1276 ,

Thanks for the update.
If you still facing any issues, feel free to reach out.

Thank You for using Microsoft Community Forum

[v-sdhruv]

Hi @Kerria1276 ,
Since we didnt hear back, we would be closing this thread.
If you need any assistance, feel free to reach out by creating a new post.

Thank you for using Microsoft Community Forum

[Kerria1276]

Apologies, I have been unwell for a week or so, so wasn't able to respond. This still isn't working for me but I'm happy for the Thread to be closed.

[v-sdhruv]

Hi @Kerria1276 ,

I hope the information provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out.
Thank You

[v-sdhruv]

Hi @Kerria1276 ,

Thanks for sharing the code. Can you just add Number.From previous to DaysUntilExpiry_Negative.
Something like ths and see if it works-

= Table.AddColumn(#"Changed Type3", "CourseExpiry_Completion_Date", each Date.AddDays([Effective_End], Number.From([DaysUntilExpiry_Negative])))

Hope this helps!

Thank You

[Kerria1276]

Here's the code from my Advanced Editor: 

[tayloramy]

@Kerria1276 This is interesting. 

Can you open the advanced editor and past in the full query code here (removing anything confidential with the source connection)

That might help us troubleshoot. 

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.

[Kerria1276]

This is my code: 

My [Effective_End] column data type is Date:

My days column (CourseExpiry_negative) is a number:

I've tried changing the days column to text and then replacing all the minus characters. I've also tried changing the data type of the Effective_End and days (CourseExpiry_negative) columns to Text and then using Date.From and Text.From in my Date.AddDays formula and it still doesn't work. 

[Kerria1276]

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):

[tayloramy]

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.

[Kerria1276]

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!

[tayloramy]

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.

[tayloramy]

Hi @Kerria1276, 

You don't need anything special for negatives - Date.AddDays accepts negative offsets. The usual culprit is type coercion: in M, as number is a type assertion (not a cast). If your column is text like "-30", as number won’t convert it; use Number.From instead. Also make sure the first argument is a date (not text).

Simple fix

= Table.AddColumn( 
    #"Changed Type3", 
    "CourseExpiry_Completion_Date", 
    each Date.AddDays( 
        Date.From([Effective_End]), 
        Number.From([DaysUntilExpiry_Negative]) 
    ), 
    type date 
) 

Defensive version (handles nulls/bad text and the Unicode minus)

= Table.AddColumn(
    #"Changed Type3",
    "CourseExpiry_Completion_Date",
    each
        let
            baseDate = try Date.From([Effective_End]) otherwise null,
            rawText = try Text.From([DaysUntilExpiry_Negative]) otherwise null,
            normalized = if rawText <> null then Text.Replace(rawText, "−", "-") else null,
            offset = try Number.From(normalized) otherwise null
        in
            if baseDate <> null and offset <> null
            then Date.AddDays(baseDate, offset)
            else null,
    type date
)

References:
- Date.AddDays docs
- Number.From docs

If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, mark this post as the solution.