Forum Discussion
Bug? - IsInNextNMonths
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...
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
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...
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
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.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"