Date.IsInPreviousNWeeks

Date.IsInPreviousNWeeks

D

The M Code Behind the Power Query M function Date.IsInPreviousNWeeks

Understanding the Date.IsInPreviousNWeeks function

The Date.IsInPreviousNWeeks function takes two parameters: the date to check and the number of weeks to go back. It returns a boolean value: true if the date falls within the previous N weeks, false otherwise. Here is an example of how to use this function:


let

Source = #table({“Date”}, {{“2022-02-01”}, {“2022-01-01”}, {“2021-12-01”}}),

PreviousWeeks = 4,

CustomFunction = (date as date) => Date.IsInPreviousNWeeks(date, PreviousWeeks),

AddColumn = Table.AddColumn(Source, “IsInPreviousWeeks”, each CustomFunction([Date])),

RemoveColumns = Table.RemoveColumns(AddColumn, {“Date”})

in

RemoveColumns


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:


(Date.ToWeekNumber(DateTime.LocalNow()) – Date.ToWeekNumber(date)) <= weeksAgo


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:


(Date.ToWeekNumber(#date(2022, 1, 1)) – Date.ToWeekNumber(date)) <= weeksAgo


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:


(Date.ToWeekNumber(date) – Date.ToWeekNumber(DateTime.LocalNow())) <= weeksAhead


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:


(Date.ToNumberOfDays(DateTime.LocalNow()) – Date.ToNumberOfDays(date)) <= (daysAgo 7)


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

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: