Date.IsInCurrentQuarter

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Date.IsInCurrentQuarter Function

Before we dive into the M code behind the Date.IsInCurrentQuarter function, it’s important to understand what the function actually does. As mentioned earlier, the function checks whether a given date is in the current quarter. This means that it takes a single argument, which is the date that you want to check.

The function returns a boolean value, which is either True or False. If the date is within the current quarter, the function returns True. If not, it returns False.

The M Code Behind Date.IsInCurrentQuarter

Now that we understand what the Date.IsInCurrentQuarter function does, let’s take a look at the M code behind it. The M code for the function is relatively simple and looks like this:

let Date.IsInCurrentQuarter = (date as date) =>

let

quarterStart = Date.StartOfQuarter(Date.From(DateTime.LocalNow())),

quarterEnd = Date.EndOfQuarter(Date.From(DateTime.LocalNow()))

in

date >= quarterStart and date <= quarterEnd

in

Date.IsInCurrentQuarter

The code starts by defining the Date.IsInCurrentQuarter function using the "let" keyword. The function takes a single argument, which is "date" and is of the data type "date".

Inside the function, two additional variables are defined using the "let" keyword: "quarterStart" and "quarterEnd". These variables are set using the "Date.StartOfQuarter" and "Date.EndOfQuarter" functions, respectively.

The "Date.StartOfQuarter" function takes a date as its argument and returns the first day of the quarter that contains that date. The "Date.EndOfQuarter" function takes a date as its argument and returns the last day of the quarter that contains that date.

Once the "quarterStart" and "quarterEnd" variables have been set, the function uses a boolean expression to check if the "date" argument is within the current quarter. This is done using the "and" operator and the ">=" and "<=" comparison operators.

Finally, the function returns the boolean value of the expression, which is either True or False.

Using Date.IsInCurrentQuarter

Now that we understand the M code behind the Date.IsInCurrentQuarter function, let's take a look at how it can be used in practice.

Suppose we have a table of sales data that includes a column for the date when each sale was made. We can use the Date.IsInCurrentQuarter function to create a new column that indicates whether each sale was made within the current quarter.

To do this, we would first need to select the "Add Column" tab in the Power Query Editor and then select "Custom Column" from the dropdown menu.

In the "Custom Column" dialog box, we would enter a name for the new column (e.g. "InCurrentQuarter") and then enter the following formula:

Date.IsInCurrentQuarter([SaleDate])

This formula tells Power Query to apply the Date.IsInCurrentQuarter function to the "SaleDate" column in our table. The resulting column will contain a boolean value that indicates whether each sale was made within the current quarter.

The Date.IsInCurrentQuarter function is a powerful tool for working with dates in Microsoft Power Query. Its simple M code makes it easy to check whether a date is within the current quarter, which can be incredibly useful for analyzing time-sensitive data.

By understanding the M code behind this function and how to use it in practice, you can take your data analysis to the next level and gain insights that might have otherwise gone unnoticed.

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)