Date.IsInPreviousYear

In this example, we create a date value representing January 1st, 2022. We then pass this value to the Date.IsInPreviousYear function, which returns false because the date is not in the previous year.

The M Code Behind Date.IsInPreviousYear

Now that we understand the function, let's take a look at the M code behind it. Here's the full code:

This code defines an anonymous function that takes a single argument, a date value. The function then does the following:

1. Uses the Date.LocalNow() function to get the current date and time in the local time zone.

2. Uses the Date.From() function to convert the current date and time to a date value.

3. Uses the Date.AddYears() function to subtract one year from the current date and time, giving us the start of the previous year.

4. Uses the Date.Year() function to get the year value from the previous year start date.

5. Uses the #date() function to create two new date values, one representing the start of the previous year and one representing the end of the previous year.

6. Uses the Date.InRange() function to check whether the input date value falls within the range of the previous year start and end dates.

Breaking Down the M Code

Let's take a closer look at each part of the M code.

The Date Function Argument

The function takes a single argument, which is a date value. This is the value that we want to check against the previous year.

Getting the Previous Year Start Date

To determine whether the input date value falls within the previous year, we need to define the start and end dates of the previous year. To get the start date, we do the following:

Here, we use the Date.LocalNow() function to get the current date and time in the local time zone. We then use the Date.From() function to convert this value to a date value. Finally, we use the Date.AddYears() function to subtract one year from the date value, giving us the start of the previous year. We use the Date.Year() function to extract the year value from the previous year start date.

Creating the Previous Year Start and End Dates

Now that we have the year value for the previous year start date, we can use the #date() function to create two new date values: one representing the start of the previous year and one representing the end of the previous year. We do this as follows:

Here, we use the #date() function to create two new date values. The first argument is the year value, which we obtained from the previous year start date. The second argument is the month value, which we set to 1 for the start date and 12 for the end date. The third argument is the day value, which we set to 1 for the start date and 31 for the end date.

Checking Whether the Date Falls in the Previous Year

Finally, we use the Date.InRange() function to check whether the input date value falls within the range of the previous year start and end dates. We do this as follows:

Here, we pass three arguments to the Date.InRange() function. The first argument is the input date value that we want to check. The second argument is the start date of the previous year that we created earlier. The third argument is the end date of the previous year that we created earlier. If the input date value falls within this range, the function returns true. Otherwise, it returns false.

In this article, we have looked at the M code behind the Power Query M function Date.IsInPreviousYear. We have seen how this function uses a combination of other M functions and operators to determine whether a given date falls in the previous year. By understanding the M code behind the function, you can gain a deeper understanding of how Power Query works with date values and how you can use the Date.IsInPreviousYear function in your own scripts.

Power Query and M Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us