Date.IsInPreviousWeek

D

Back to top

T

Back to top

#

Back to top

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

In this article, we will explore the M code behind the Power Query M function Date.IsInPreviousWeek.

Overview of the Date.IsInPreviousWeek function

The Date.IsInPreviousWeek function is a useful function in Power Query that allows you to determine whether a date falls within the previous week. The function takes a single argument, which is the date to be tested, and returns a Boolean value indicating whether the date falls within the previous week.

Here’s an example of how to use the Date.IsInPreviousWeek function:

= Date.IsInPreviousWeek(#date(2022, 5, 2))

This formula returns the value `true` if the date `May 2, 2022` falls within the previous week, and `false` otherwise.

Understanding the M code behind the Date.IsInPreviousWeek function

To understand the M code behind the Date.IsInPreviousWeek function, we need to look at the function definition in the Advanced Editor of Power Query. To do this, follow these steps:

1. Open Power Query and create a new query.

2. Click on the "View" tab and select "Advanced Editor."

3. In the Advanced Editor, enter the following code:

(Date as date) as logical =>

let

PreviousWeekStart = Date.AddDays(Date.StartOfWeek(Date), -7),

PreviousWeekEnd = Date.AddDays(PreviousWeekStart, 7),

IsInPreviousWeek = Date >= PreviousWeekStart and Date < PreviousWeekEnd

in

IsInPreviousWeek

4. Click on the "Done" button to close the Advanced Editor.

The code above defines a custom function called `Date.IsInPreviousWeek`, which takes a single argument called `Date` of type `date`. The function then defines three variables using the `let` keyword:

- `PreviousWeekStart`: Calculates the start of the previous week by subtracting 7 days from the start of the current week.

- `PreviousWeekEnd`: Calculates the end of the previous week by adding 7 days to the start of the previous week.

- `IsInPreviousWeek`: A Boolean expression that returns `true` if the `Date` falls within the previous week.

Finally, the function returns the result of the `IsInPreviousWeek` expression.

How the Date.IsInPreviousWeek function works

The `Date.IsInPreviousWeek` function works by calculating the start and end dates of the previous week using the `Date.StartOfWeek` function and some basic arithmetic. The function then checks whether the input `Date` falls within the previous week by comparing it to the start and end dates of the previous week using a Boolean expression.

For example, suppose we want to test whether the date `May 2, 2022` falls within the previous week. The function would calculate the start of the previous week as `April 25, 2022` and the end of the previous week as `May 2, 2022`. The function would then check whether the input `Date` falls within this range by comparing it to the start and end dates of the previous week using a Boolean expression. In this case, the input date `May 2, 2022` falls within the previous week, so the function returns `true`.

In this article, we explored the M code behind the Power Query M function Date.IsInPreviousWeek. We saw how the function works by calculating the start and end dates of the previous week and checking whether the input date falls within this range using a Boolean expression. Understanding the M code behind this function can help you create more complex custom functions in Power Query and improve your data analysis and reporting capabilities.

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)