Date.IsInPreviousQuarter

D

Back to top

T

Back to top

#

Back to top

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

One of the many useful functions available in Power Query is the Date.IsInPreviousQuarter function. This function allows you to determine if a given date falls within the previous quarter. In this article, we will take a closer look at the M code behind the Date.IsInPreviousQuarter function and how it works.

What is Power Query M Language?

Power Query M language is a functional programming language that is used to create queries in Power Query. It is a powerful language that allows you to perform a wide range of data transformations, including filtering, sorting, grouping, and more.

The M language is based on the functional programming paradigm, which means that it is designed to be highly modular and composable. This makes it easy to create complex queries by combining simple building blocks together.

Understanding the Date.IsInPreviousQuarter Function

The Date.IsInPreviousQuarter function is a simple function that takes a single argument, a date value, and returns a Boolean value that indicates whether the date falls within the previous quarter. The function is defined as follows:

Date.IsInPreviousQuarter(date as any) as logical

The function takes a single parameter, "date," which can be any date value, including a date literal or a column reference. The function returns a logical value, which is either true or false, depending on whether the date falls within the previous quarter.

The M Code Behind the Date.IsInPreviousQuarter Function

The M code behind the Date.IsInPreviousQuarter function is relatively straightforward. The function is implemented using a series of M language expressions that perform various date calculations.

The first expression in the function calculates the start date of the previous quarter. This is done using the Date.StartOfQuarter function, which takes the current date and returns the start date of the quarter that the current date falls within. The function then subtracts three months from the start date to get the start date of the previous quarter.

let

StartOfQuarter = Date.StartOfQuarter(date),

StartOfPrevQuarter = Date.AddMonths(StartOfQuarter, -3),

The next expression in the function calculates the end date of the previous quarter. This is done using the Date.EndOfQuarter function, which takes the current date and returns the end date of the quarter that the current date falls within. The function then subtracts three months from the end date to get the end date of the previous quarter.

EndOfQuarter = Date.EndOfQuarter(date),

EndOfPrevQuarter = Date.AddMonths(EndOfQuarter, -3),

Finally, the function checks whether the input date falls within the range of the previous quarter. This is done using the Date.InRange function, which takes a start date, an end date, and a target date, and returns a logical value indicating whether the target date falls within the range of the start and end dates.

IsInPrevQuarter = Date.InRange(date, StartOfPrevQuarter, EndOfPrevQuarter)

in

IsInPrevQuarter

The Date.IsInPreviousQuarter function is a simple yet powerful function that can be used to determine whether a given date falls within the previous quarter. The function is implemented using a series of M language expressions that perform various date calculations.

By understanding the M code behind the Date.IsInPreviousQuarter function, you can gain a deeper understanding of how Power Query works and how to create your own custom functions. Whether you are a beginner or an experienced user, knowing the M language can help you to create more powerful and efficient queries 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)