Date.IsInPreviousNQuarters

D

Back to top

T

Back to top

#

Back to top

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

What is the Date.IsInPreviousNQuarters function?

The Date.IsInPreviousNQuarters function is a Power Query M function that returns a boolean value indicating whether a given date falls within the previous N quarters. The function takes two arguments: a date value and an integer value indicating the number of quarters to check.

Here is the syntax for the Date.IsInPreviousNQuarters function:

Date.IsInPreviousNQuarters(date as any, quarters as number) as logical

How does the function work?

The Date.IsInPreviousNQuarters function works by first calculating the start and end dates for the previous N quarters based on the current date. It then checks whether the given date falls within this date range and returns a boolean value accordingly.

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

let

IsInPreviousNQuarters = (date as any, quarters as number) as logical =>

let

currentDate = Date.From(DateTime.LocalNow()),

startQuarter = Date.StartOfQuarter(Date.AddQuarters(currentDate, -quarters)),

endQuarter = Date.EndOfQuarter(Date.AddQuarters(currentDate, -1))

in

date >= startQuarter and date <= endQuarter

in

IsInPreviousNQuarters

The code first defines a function called IsInPreviousNQuarters with two arguments: date and quarters. It then declares three variables: currentDate, startQuarter, and endQuarter. currentDate is set to the current date using the DateTime.LocalNow() function. startQuarter is set to the start date of the quarter N quarters ago using the Date.StartOfQuarter() and Date.AddQuarters() functions. endQuarter is set to the end date of the previous quarter using the Date.EndOfQuarter() and Date.AddQuarters() functions.

Finally, the function checks whether the given date falls within the start and end dates of the previous N quarters and returns a boolean value accordingly.

How can the Date.IsInPreviousNQuarters function be used?

The Date.IsInPreviousNQuarters function can be used in a variety of data transformation scenarios. For example, it can be used to filter a table of sales data to include only sales that occurred in the previous N quarters. Here is an example of how this can be done:

1. Load the sales data into Power Query.

2. Add a custom column using the Date.IsInPreviousNQuarters function with the desired number of quarters.

3. Filter the table to include only rows where the custom column is true.

Here is the M code for this example:

let

Source = Excel.Workbook(File.Contents(“C:SalesData.xlsx”), null, true),

Sales_Table = Source{[Item=”Sales”,Kind=”Table”]}[Data],

Custom1 = Table.AddColumn(Sales_Table, “IsInPrev4Quarters”, each Date.IsInPreviousNQuarters([Date], 4)),

FilteredTable = Table.SelectRows(Custom1, each ([IsInPrev4Quarters] = true))

in

FilteredTable

This code loads the sales data from an Excel workbook, adds a custom column using the Date.IsInPreviousNQuarters function with a value of 4 for the number of quarters, and filters the table to include only rows where the custom column is true.

The Date.IsInPreviousNQuarters function is a useful Power Query M function that can be used to solve real-world data transformation challenges. By understanding the M code behind this function and how it works, users can leverage its power to retrieve, transform, and load data from a variety of sources.

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)