Date.IsInPreviousNWeeks

In this code, we create a table with a Date column, define the number of previous weeks to check (4), and create a custom function that uses the Date.IsInPreviousNWeeks function. We then add a new column to the table that applies this function to each value in the Date column, and finally remove the original Date column to keep only the boolean result.

Breaking down the M code

The M code behind the Date.IsInPreviousNWeeks function is straightforward:

This code calculates the week number of the current date (using DateTime.LocalNow()), subtracts the week number of the date to check (using Date.ToWeekNumber()), and compares the result to the number of previous weeks specified (weeksAgo). If the difference is less than or equal to weeksAgo, the function returns true, otherwise it returns false.

Customizing the function

If you need to modify the Date.IsInPreviousNWeeks function for your specific needs, you can do so by creating a custom function that uses the M code we just discussed. Here are some examples of how you can customize this function:

Change the reference date

By default, the Date.IsInPreviousNWeeks function uses the current date as the reference point. If you need to use a different date, you can modify the M code like this:

In this code, we replace DateTime.LocalNow() with a specific date (#date(2022, 1, 1)). This will calculate the week number based on January 1, 2022, instead of the current date.

Check for future weeks

If you need to check if a date falls within the next N weeks instead of the previous N weeks, you can modify the M code like this:

In this code, we swap the order of the week number calculations and change the comparison to weeksAhead. This will return true if the date falls within the next N weeks, instead of the previous N weeks.

Change the granularity

By default, the Date.IsInPreviousNWeeks function checks for weeks. If you need to check for a different granularity, such as days or months, you can modify the M code like this:

In this code, we use the Date.ToNumberOfDays() function instead of Date.ToWeekNumber() to calculate the difference in days between the reference date and the date to check. We then multiply the daysAgo parameter by 7 to convert it from weeks to days.

The Date.IsInPreviousNWeeks function is a simple yet powerful M function in Power Query that allows you to check if a date falls within the previous N weeks. By understanding the M code behind this function and customizing it for your specific needs, you can take your date calculations to the next level.

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

Upcoming Courses

Contact Us