Date.IsInNextQuarter

D

Back to top

T

Back to top

#

Back to top

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

Understanding the Date.IsInNextQuarter Function

The Date.IsInNextQuarter function is a useful function in Power Query that allows you to check whether a given date falls in the next quarter. The function returns a Boolean value, which is either True or False. If the date falls in the next quarter, the function will return True, otherwise, it will return False.

The Syntax for the Date.IsInNextQuarter Function

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

Date.IsInNextQuarter(date as any) as logical

The function takes a single argument, which is a date as any data type. The function returns a logical data type, which is either True or False.

The M Code Behind the Date.IsInNextQuarter Function

The M code behind the Date.IsInNextQuarter function is as follows:

let

IsInNextQuarter = (date as any) as logical =>

let

CurrentQuarter = Number.RoundUp(Date.Month(date) / 3),

CurrentQuarterEndDate = Date.EndOfQuarter(date),

NextQuarterStartDate = Date.AddDays(CurrentQuarterEndDate, 1),

NextQuarterEndDate = Date.EndOfQuarter(Date.AddQuarters(date, 1)),

IsInNextQuarter = if date >= NextQuarterStartDate and date <= NextQuarterEndDate then true else false

in

IsInNextQuarter

in

IsInNextQuarter

The M code behind the Date.IsInNextQuarter function is a bit complex, but it is not difficult to understand. The function takes a date as its input and returns a Boolean value indicating whether the date falls in the next quarter.

Breaking Down the M Code

Let's break down the M code behind the Date.IsInNextQuarter function.

1. CurrentQuarter

The first step in the M code is to determine the current quarter. This is done using the following code:

CurrentQuarter = Number.RoundUp(Date.Month(date) / 3)

The Date.Month function returns the month number of a given date. Number.RoundUp function is used to round up the result of the division of the month number by 3 to the next integer. This gives us the current quarter.

2. CurrentQuarterEndDate

The next step is to determine the end date of the current quarter. This is done using the following code:

CurrentQuarterEndDate = Date.EndOfQuarter(date)

The Date.EndOfQuarter function returns the last day of the quarter in which the given date falls.

3. NextQuarterStartDate

The third step is to determine the start date of the next quarter. This is done using the following code:

NextQuarterStartDate = Date.AddDays(CurrentQuarterEndDate, 1)

The Date.AddDays function is used to add a single day to the end date of the current quarter. This gives us the start date of the next quarter.

4. NextQuarterEndDate

The fourth step is to determine the end date of the next quarter. This is done using the following code:

NextQuarterEndDate = Date.EndOfQuarter(Date.AddQuarters(date, 1))

The Date.AddQuarters function is used to add one quarter to the given date. The Date.EndOfQuarter function is then used to determine the last day of the quarter in which the new date falls. This gives us the end date of the next quarter.

5. IsInNextQuarter

The final step is to determine whether the given date falls in the next quarter. This is done using the following code:

IsInNextQuarter = if date >= NextQuarterStartDate and date <= NextQuarterEndDate then true else false

If the given date falls between the start date and end date of the next quarter, the function will return True. Otherwise, it will return False.

The Date.IsInNextQuarter function is a powerful function in Power Query that allows you to check whether a given date falls in the next quarter. The M code behind the function may be complex, but it is not difficult to understand. By breaking down the M code, you can gain a better understanding of how the function works and how it can be used to shape data the way you want it.

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)