Date.IsInYearToDate

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the Date.IsInYearToDate function, and provide a detailed explanation of how it works.

What is the Date.IsInYearToDate Function?

The Date.IsInYearToDate function in Power Query M is a Boolean function that takes a date value as its argument, and returns true if the date falls within the current year-to-date period, and false otherwise.

The function has the following syntax:

Date.IsInYearToDate(date as date) as logical

The argument 'date' is a required input, and must be a valid date value.

How Does the Function Work?

The Date.IsInYearToDate function works by first calculating the start date of the current year-to-date period, and then comparing the input date to this start date.

To calculate the start date of the current year-to-date period, the function uses the following expression:

StartDate = Date.StartOfYear(DateTime.LocalNow())

This expression returns the start date of the current year, which is then used to calculate the start date of the year-to-date period:

YearToDateStart = Date.StartOfDay(Date.AddYears(StartDate, -1))

This expression subtracts one year from the start date of the current year, and then calculates the start of the day for this date. This gives us the start date of the year-to-date period.

Once the start date of the year-to-date period is calculated, the function compares the input date to this start date using the following expression:

date >= YearToDateStart and date <= DateTime.LocalNow()

If the input date falls within the year-to-date period, the expression returns true, and if it falls outside the year-to-date period, the expression returns false.

Examples of Usage

Let's look at a few examples of how the Date.IsInYearToDate function can be used in Power Query M.

Example 1: Calculating Year-to-Date Sales

Suppose we have a table of sales data with the following columns:

- Date: The date of the sale

- Amount: The amount of the sale

To calculate the year-to-date sales figures, we can use the following expression:

YearToDateSales = List.Sum(

Table.SelectRows(

Sales,

each Date.IsInYearToDate([Date])

)[Amount]

)

This expression filters the sales table to include only the rows where the date is within the year-to-date period, and then calculates the sum of the Amount column for these rows.

Example 2: Flagging Late Payments

Suppose we have a table of invoice data with the following columns:

- InvoiceDate: The date the invoice was issued

- PaymentDueDate: The date payment is due

- PaymentReceivedDate: The date payment was received (if any)

To flag late payments, we can use the following expression:

LatePayments = Table.AddColumn(

Invoices,

“LatePayment”,

each

Date.IsInYearToDate([PaymentDueDate])

and ([PaymentReceivedDate] = null or [PaymentReceivedDate] > [PaymentDueDate])

)

This expression adds a new column to the Invoices table called LatePayment, which is a Boolean value indicating whether the payment was received late. The LatePayment column is calculated using the Date.IsInYearToDate function to check if the payment due date falls within the year-to-date period, and the PaymentReceivedDate column to check if the payment was received late.

The Date.IsInYearToDate function in Power Query M is a powerful tool that allows users to check whether a given date falls within the current year-to-date period. By understanding the M code behind this function, users can create complex calculations and analyses that are tailored to their specific needs.

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)