Date.IsInPreviousNYears

Date.IsInPreviousNYears is a Power Query M function that indicates whether the given date(time) value occurs during the previous number of years, as determined by the system. The function returns a boolean value (true or false).

Compatible with: Power BI Service Power BI Desktop Excel Microsoft 365

Syntax

Date.IsInPreviousNYears(
   dateTime as any,
   years as number,
) as nullable logical

Description

Date.IsInPreviousNYears determines if a given value (of type date, datetime, or datetimezone) is within a specified number of years before the current system date and time, excluding the current year. It requires a years argument.

Examples

Let’s explore two straightforward examples to understand how the Date.IsInPreviousNYears function works.

Identifying Dates in the Previous N Years

Imagine you have a calendar table and you want to add a column that marks dates as true if they fall within the last n years. You can achieve this by using the Date.IsInPreviousNYears function on your [Date] column, with n being the number of years you want to check.

Date.IsInPreviousNYears( [Date], n ) // Output depends on [Date] column and n

This expression returns true for any date in the [Date] column that falls within the previous n years. To test if your values fall within the last 2 years, you set n to 2:

Date.IsInPreviousNYears( [Date], 2 )

If today is July 6, 2024, this function will return true for all rows with dates between January 1, 2022, and December 31, 2023. Here’s a screenshot showing this:

You can see that dates within the previous 2 years are marked with true.

Using the Current Date and Time

If we retrieve the current date (generated by DateTime.FixedLocalNow) and shift it back 2 years, we can check if it falls within the last 4 years by using:

// Output: true
Date.IsInPreviousNYears( Date.AddYears( DateTime.FixedLocalNow(), -2 ), 4 )

Related functions

Other functions related to Date.IsInPreviousNYears are:

• Date.IsInPreviousNDays
• Date.IsInPreviousNMonths
• Date.IsInPreviousNQuarters
• Date.IsInPreviousNWeeks

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/powerquery-m/date-isinpreviousnyears