# 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: ```

``` 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: ```

Invoices,

“LatePayment”,

each

Date.IsInYearToDate([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) Power Query and M Intensive Training Course £1,050.00 – £26,550.00 Select optionsContinue Loading Done Power Query and M Introduction £395.00 – £9,750.00 Select optionsContinue Loading Done Power Query and M Intermediate £395.00 – £9,750.00 Select optionsContinue Loading Done Power Query and M Advanced £395.00 – £9,750.00 Select optionsContinue Loading Done Upcoming Courses Contact Us Subject Your Name (required) Company/Organisation Email (required) Telephone Training Course(s) Power BI Intensive TrainingPower BI introduction Power BI IntermediatePower BI AdvancedDAXPower Query MPower BI CertificationPower BI AdministrationPower PlatformPower AutomatePower AppsOTHER Your Message Upload Example Document(s) (Zip multiple files) ```
``` ```
``` ```
``` ```
``` ```
``` ```