Date.IsInPreviousNMonths

Date.IsInPreviousNMonths

D

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)

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: