Date.IsInNextNMonths

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Date.IsInNextNMonths Function

The Date.IsInNextNMonths function is a handy tool that allows you to filter data based on dates falling within the next N number of months. It takes two arguments:

– Date: the date column you want to filter

– N: the number of months you want to filter for

The function returns a true or false value, indicating whether or not a date falls within the next N months.

The M Code Behind Date.IsInNextNMonths

The M code behind the Date.IsInNextNMonths function is relatively simple. Let’s take a look at the code:

(Date as date, N as number) =>

Date.IsInNextNMonths = (date) =>

let

today = Date.StartOfDay(DateTime.LocalNow()),

nextNMonths = Date.AddMonths(today, N)

in

date >= today and date < nextNMonths

As you can see, the function takes two arguments: the date column and the number of months. It then returns a Boolean value indicating whether or not the date falls within the next N months.

The code does this by first defining the variable "today" as the start of the current day using the Date.StartOfDay function, which extracts the date portion of the datetime value.

Next, the code defines the variable "nextNMonths" as the date N months from today using the Date.AddMonths function. This is the end date of the range we are interested in.

Lastly, the code checks if the date falls within the range of today and the next N months. If the date is greater than or equal to today and less than the next N months, the function returns true, indicating that the date falls within the next N months.

Using Date.IsInNextNMonths to Filter Data

Now that we understand how the Date.IsInNextNMonths function works, let's take a look at how to use it to filter data.

Suppose we have a table of sales data that includes columns for date and sales amount. We want to filter this data to show only records where the sale happened within the next 4 months. Here's how we can do that using the Date.IsInNextNMonths function:

1. Load the data into Power Query by copying and pasting the data into a new query window or by connecting to a data source.

2. Select the Date column by clicking on the column header.

3. Click on the "Add Column" tab in the ribbon and select "Custom Column".

4. Enter the following formula in the formula bar:

= Date.IsInNextNMonths([Date], 4)

5. Click "OK" to create the custom column.

6. Filter the new column to show only records where the value is "true".

That's it! You now have a filtered table that shows only sales records that fall within the next 4 months.

The Date.IsInNextNMonths function is a powerful tool that allows you to filter data based on dates falling within the next N number of months. By understanding the M code behind this function, you can gain a deeper understanding of how it works and how to use it to filter your own data. With this knowledge, you can automate the task of filtering data and save yourself valuable time and effort.

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)