Date.IsInPreviousMonth

D

Back to top

T

Back to top

#

Back to top

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

This function comes in handy when you need to filter data based on whether it falls within the previous month. For instance, if you want to extract data from your sales database for the previous month, you can use the Date.IsInPreviousMonth function to filter out the relevant data.

In this article, we will explore the M code behind the Date.IsInPreviousMonth function and how it works.

Understanding the Date.IsInPreviousMonth Function

The Date.IsInPreviousMonth function is a Power Query M function that checks whether a given date falls within the previous month. The function takes a date value as its argument and returns a logical value (True or False) indicating whether the date falls within the previous month.

The syntax for the Date.IsInPreviousMonth function is as follows:

Date.IsInPreviousMonth(date as any) as logical

Here, the `date` argument can be any date value, such as a column of date values in a table.

The M Code Behind the Function

The M code behind the Date.IsInPreviousMonth function is relatively simple. It uses the Date.Year, Date.Month, and Date.DaysInMonth functions to determine the start and end dates of the previous month and then checks whether the input date falls within this range.

Here is the M code for the Date.IsInPreviousMonth function:

(date as any) =>

let

currentMonth = Date.Month(date),

currentYear = Date.Year(date),

daysInMonth = Date.DaysInMonth(#date(currentYear, currentMonth, 1)),

startDate = #date(currentYear, currentMonth-1, 1),

endDate = #date(currentYear, currentMonth-1, daysInMonth)

in

if startDate <= date and date <= endDate then true else false

Let's break down the code step by step:

1. The function takes a date value as its argument and assigns it to the `date` variable.

2. The `currentMonth` variable is assigned the month value of the input date using the Date.Month function.

3. The `currentYear` variable is assigned the year value of the input date using the Date.Year function.

4. The `daysInMonth` variable is assigned the number of days in the previous month using the Date.DaysInMonth function. The function takes a date value as its argument, which in this case is the first day of the current month (`#date(currentYear, currentMonth, 1)`).

5. The `startDate` variable is assigned the first date of the previous month using the `currentMonth-1` argument in the #date function.

6. The `endDate` variable is assigned the last date of the previous month using the `daysInMonth` variable and the `currentMonth-1` argument in the #date function.

7. The function then checks whether the input date falls within the range of the previous month (from `startDate` to `endDate`) using an if statement. If the input date falls within this range, the function returns True. Otherwise, it returns False.

In this article, we have explored the M code behind the Date.IsInPreviousMonth function in Power Query. We have seen how the function uses the Date.Year, Date.Month, and Date.DaysInMonth functions to determine the start and end dates of the previous month and check whether the input date falls within this range.

The Date.IsInPreviousMonth function is a powerful tool for filtering data based on whether it falls within the previous month. By understanding the M code behind this function, you can further customize and extend your data transformation workflows in Power Query.

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)