Date.IsInNextNWeeks

The code defines a function called IsInNextNWeeks that takes two arguments: the date to be tested and the number of weeks from the current date. The function returns a logical value indicating whether the date falls within the specified number of weeks.

The function first gets the current date using the DateTime.LocalNow() function, which returns the current date and time in the local time zone. The Date.From function is then used to extract the date from the current date and store it in a variable called currentDate.

Next, the function calculates the end date by adding the specified number of weeks to the current date using the Date.AddDays function. The result is stored in a variable called endDate.

Finally, the function checks whether the date to be tested is less than or equal to the end date. If it is, the function returns true, indicating that the date falls within the specified number of weeks. If it is not, the function returns false.

Using Date.IsInNextNWeeks

The Date.IsInNextNWeeks function can be used in a variety of ways to simplify data transformation tasks. Here are some examples:

Example 1: Filtering Data

Suppose you have a table of sales data that includes a column called OrderDate. You want to filter the table to include only the sales that occurred within the next two weeks. Here's how you can do it using the Date.IsInNextNWeeks function:

The code creates a table with four rows, each representing a different order date. The Table.SelectRows function is then used to filter the table to include only the rows where the OrderDate falls within the next two weeks. The Date.IsInNextNWeeks function is used as the filter function, with the OrderDate column passed as the first argument and the number 2 passed as the second argument.

The resulting table includes only the rows where the OrderDate falls within the next two weeks.

Example 2: Calculating Rolling Averages

Suppose you have a table of sales data that includes a column called SalesDate and a column called SalesAmount. You want to calculate the rolling average of sales over the next four weeks for each SalesDate. Here's how you can do it using the Date.IsInNextNWeeks function:

The code creates a table with four rows, each representing a different sales date and sales amount. The Table.Group function is then used to group the rows by SalesDate and calculate the rolling average of sales over the next four weeks for each SalesDate.

The Date.IsInNextNWeeks function is used to select the rows that fall within the next four weeks for each SalesDate. The List.Average function is then used to calculate the average of the SalesAmount column for those rows. The resulting table includes a new column called RollingAverage, which contains the rolling average of sales over the next four weeks for each SalesDate.

The Date.IsInNextNWeeks function is a powerful tool for simplifying data transformation tasks in Power Query M. By understanding the M code behind this function and learning how to use it effectively, you can streamline your data transformation workflows and save time and effort.

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

Upcoming Courses

Contact Us