Date.IsInPreviousNMonths

D

Back to top

T

Back to top

#

Back to top

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

How Does Date.IsInPreviousNMonths Work?

The function Date.IsInPreviousNMonths takes two parameters, the first is the date column you want to filter on, and the second is the number of months in the past you want to include. The function will return all rows where the date is within the specified range.

Here’s an example of how this function can be used. Suppose you have a dataset containing sales data for the past year, and you want to filter out all sales that occurred more than six months ago. Here’s how you can use the Date.IsInPreviousNMonths function to accomplish this:

= Table.SelectRows(Sales, each Date.IsInPreviousNMonths([Sale Date], 6))

In this example, "Sales" is the name of the table containing the sales data, "Sale Date" is the name of the column containing the date of each sale, and "6" is the number of months in the past that we want to include.

The M Code Behind Date.IsInPreviousNMonths

If you're curious about the M code behind the Date.IsInPreviousNMonths function, here it is:

(date as any, months as number) as logical =>

let

fromDate = Date.AddMonths(DateTime.LocalNow(), -months),

toDate = DateTime.LocalNow()

in

fromDate <= date and date <= toDate

Let's break down this code a bit. The function takes two parameters, "date" and "months". The "date" parameter is the date that we're checking against, and the "months" parameter is the number of months in the past that we want to include.

The first line of the function defines two variables, "fromDate" and "toDate". "fromDate" is calculated by subtracting the specified number of months from the current date (which is obtained using the DateTime.LocalNow() function), and "toDate" is simply the current date.

The second line of the function uses the "<= " and ">= " operators to check whether the "date" falls within the specified range.

In conclusion, the Date.IsInPreviousNMonths function is a powerful tool for filtering data based on a specific date range. By specifying a number of months in the past, you can easily filter out data that falls outside of your desired range. Understanding the M code behind this function can help you better understand how it works and how it can be customized to fit your specific needs.

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)