Bug? - IsInNextNMonths

[chelynne]

I'm experiencing some very weird results with this powerquery formula. I know the formula is a bit janky, but it's not working as it should.  

 1st pic - formula,

2nd pic - weird result. June 26th is NOT within the next 12 months. 3rdpic - formula not properly populating a few of the results that do indeed have expiry dates. 

 

 

 

[edhans · Accepted solution]

Both Date.IsInNextNMonths and Date.IsInPreviousMonths ignore the current month, because the current month is not in the next months, you cannot use either as an absolute for future/past. You'd need to use an OR condition with Date.IsInCurrentMonth. and is after today.

for example:

Date.IsInNextNMonths([Date], 6) or (Date.IsInCurrentMonth([Date]) and [Date] > DateTime.Date(DateTime.LocalNow()))

With today being June 14, 2022...

[chelynne]

1st photo - 26 June 23, is showing within 12 months. It's only the 15th, it should be showing 18.

2, 3, 4 , the updated formula I'm trying and the error it created. 

[edhans]

I cannot read those photos clearly, and stuff is cut off.

I mocked up some quick data and it works just fine. The June and July dates in the future show expiring. You need to really test for current month and in the future, and next 1 month.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtM3NNU3MjAyUorViVYy1Tc0Q/DMUeQsUHhmCJWxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"Date", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Date", type date}}),
    #"Added Expiring" = 
        Table.AddColumn(
            #"Changed Type", 
            "Expiring in Next 2 Months", 
            each Date.IsInNextNMonths([Date], 1) or (Date.IsInCurrentMonth([Date]) and [Date] >= DateTime.Date(DateTime.LocalNow())),
            Logical.Type
        )
in
    #"Added Expiring"

Again, if you want some detailed help, I'll repeat my request. If you can post data per below with expected output we can assist with a formula that works.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[chelynne]

Perfect, so I got the 2 month to work, but I'm still unhappy with the time intelligence. 

With 'else if Date.IsInNMonths([Security Expiration Date]), 12) then "12" '
I expect June 26th 2023, to come out as 18, because it's not in the next 12 months, but got 12.

When I changed it to "else if Date.IsInNMonths([Security Expiration Date]), 11) then "12", I got 18...

But I also got 18 on 7 June -12 June as well, which is wrong.

Do I have to use some complicated formula of 
else if Date.IsInNMonths([Security Expiration Date]), 12) - current day or something?

[edhans]

June 26, 2023 is in the next 12 months. This is June 2022. The next 12 months are July, August, Sept...April, May, June 2023.

You are wanting it to look at the next 365 days or something, and if that is the case, use Date.IsInNextNDays([Date],365) which would seem to work better for you unless a leap year messed it up by one day for a particular calc. But remember, Date.IsInNextNDays(#date(2022,6,16),30) will return false, because today is not in the next N days. You still have to account for the current day as I showed in previous posts.

So the answers you are getting from Power Query are not wrong. You just have a different expectation of what the formula is calculating.

[edhans · Accepted solution]

Both Date.IsInNextNMonths and Date.IsInPreviousMonths ignore the current month, because the current month is not in the next months, you cannot use either as an absolute for future/past. You'd need to use an OR condition with Date.IsInCurrentMonth. and is after today.

for example:

Date.IsInNextNMonths([Date], 6) or (Date.IsInCurrentMonth([Date]) and [Date] > DateTime.Date(DateTime.LocalNow()))

With today being June 14, 2022...

[chelynne · Accepted solution]

I thought this made sense, but it's throwing an error for the affected lines. I'll update with errors soon.

It also did not fix the problem of June 26th showing within the next 12 months. 

I still think there's a bug regarding what it considers a month

[edhans]

No, there are no bugs here. I've used both extensively.  If you can post data per below with expected output we can assist with a formula that works.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[chelynne]

My expected result. I want to see things expiring  within the next 2 months, including this month as 2. 

I want to things expiring within the next year(it's now 15 June 22, so before 15 June 23) as 12, if nothing else has been written yet. 

 Expected Result ^ 18, not 12. That date is not within the next 12 months... or do I need to change it to 11 months if it doesn't count the current month? This isn't super intuitive. 

 New formula I'm trying. I changed the "2" line to -->
else if Date.IsInNextNMonths([Security Expiry Date]), 2) or (Date.IsInCurrentMonth([Security Expiry Date]) and [Security Expiry Date] > DateTime.LocalNow) then "2"