Power Query Date Functions | Power Query Today

One common task in data analysis is working with dates. Power BI Power Query provides functions for working with dates, including getting the current date and other date-related functions.

In the tutorial, I will discuss Power Query Today Date in Power BI and Power Query Today Minus 7 days. Also, we will cover the topics below:

• Power query current date minus 1 day in Power BI
• Power query date minus 1 month
• Power query date minus 1 year in Power BI
• Power query date between
• Power query date difference from today

Power BI Power Query Today

In this example, we create the current date in Power Query.

To get the current date in Power Query, you can use the function “=Date.From(DateTime.LocalNow())” in the Formula bar of the Power Query Editor.

Now follow the below steps:

1. Open Power BI Desktop, Then open Power Query Editor.

2. Go to the Home tab, then expand New Source and click on Blank Query.

3. In the formula bar, put the bellow expression.

= Date.From(DateTime.LocalNow())

Where:

• Date.From: This part indicates that we want to create a date object.
• (DateTime.LocalNow()): This function gets the current date and time based on the local time zone.

4. Then, you can see the current date in Power Query Editor.

This way, you can get the current date in the Power BI Power query.

Power Query Current Date Minus 1 Day in Power BI

Let’s see how to use the Power Query Current Date minus 1 day in Power BI.

Suppose Today’s date is April 11, 2024. If we subtract 1 day, it will be April 10, 2024.

Now follow the below steps:

1. Open Power BI Desktop, then go to Power Query Editor.

2. Go to the Home tab, then expand New Source and click on Blank Query.

3. In the formula bar, put the bellow expression.

= Date.From(Date.AddDays(DateTime.LocalNow(), -1))

Where:

• Date.From() = This function converts a given date and time into just a date, removing the time component.
• Date.AddDays() = This function calculates a new date by adding or subtracting a specific number of days to/from a given date.
• DateTime.LocalNow() = This part gets the current date and time based on your local system settings.
• -1 = This number represents the number of days to subtract from the current date.

4. Then, you can see the power query today minus the date in Power BI.

Power Query Today Minus 7 days

Today, it’s April 11, 2024. If I subtract 7 days, it will be April 4, 2024. Now, let’s see how to do it in Power Query Editor:

Open the Power Query editor. Then, under the Home tab, expand New Source and click on Blank Query. In the formula bar, put below the DAX expression.

= Date.From(Date.AddDays(DateTime.LocalNow(), -7))

Where:

• Date.From() = This function converts a given date and time into just a date, removing the time component.
• Date.AddDays() = This function calculates a new date by adding or subtracting a specific number of days to/from a given date.
• DateTime.LocalNow() = This part gets the current date and time based on your local system settings.
• -7 = This number represents the number of days to subtract from the current date.

This way, power query today minus 7 days, you can do it.

Power Query Date Minus 1 Month

Let’s see how to subtract 1 month from the date column using the Power query editor.

For this example, I will subtract 1 month from each date in the table below.

Now follow the below steps:

1. Open Power BI Desktop, then go to Power Query Editor.

2. Then, in the Add Column tab, click Custom Column.

3. When the custom column window opens, provide the column name and use the following formula to subtract 1 month from the date column.

Date.AddMonths([Date], -1)

Where:

• Date.AddMonths = This part indicates that we’re dealing with a date object and want to add or subtract months from it.
• [Date] = The [Date] likely represents a variable or a cell reference that holds a date value.
• -1 = This indicates we want to subtract one month from the date.

Once you click on Ok, you’ll see the column added to the table, containing the date values with one month subtracted from the date columns.

This way, you can subtract 1 month from the date column using the Power query editor.

Power Query Date Minus 1 Year in Power BI

We will learn how to subtract one year from a date using the Power Query editor in Power BI.

For this example, we will use the table below.

To subtract 1 year from the date column, follow these steps:

1. Open Power BI Desktop, then go to Power Query Editor.

2. Under the Home tab, click Enter Data.

3. Then the below Create Table dialog box will appear. We use the ‘+‘ symbols to add new columns and rows.

4. After that, you can add data to the table by typing it into the grid. Then, specify the table name under the “Name” field and click “Load.”

5. Then, in the Add Column tab, click Custom Column.

6. When the custom column window opens, provide the column name and use the following formula to subtract 1 Year from the date column.

Date.AddYears([Date], -1)

Where:

• “Date” = This refers to a specific point in time, like today’s date.
• “.AddYears” = This means to increase or decrease the year part of the date.
• “([Date], -1)” = This part tells the computer to subtract 1 year from the given date.

7. Now you can see the column get added to the table with minus 1 year.

This is an example of subtracting 1 year from a date using Power Query.

Power Query Date Between

Now, we see how to calculate days between two dates using the Power Query editor in Power BI.

For this example, I’m using the table below:

Follow the below steps to do this:

1. Open the Power Query Editor to load the above data.

2. Then, in the Add Column tab, click Custom Column.

3. When the custom column window opens, provide the column name and use the following formula.

Duration.Days([End Date]-[Start Date])

Where:

• Duration = This is how long something lasts or continues. For example, if you go on a trip, the duration of your trip is how many days you’re away.
• Days() = This is a function that calculates the number of days within a given time period. It tells you how many days there are in total.
• [End Date] – [Start Date] = This is a mathematical operation where you subtract the starting date from the ending date. It gives you the time period between the two dates.

4. Next, you will see the No of days added to the table in the Query Editor.

This is an example of using Power Query to extract days between a specific date range.

Power Query Date Difference From Today

Let’s say you’re managing a small business, and you want to track how long it takes for your orders to be fulfilled from the order date to today’s date.

In this scenario, we have an Excel file named Orders, which contains columns for OrderID, OrderDate, Customer Name, Product, Quantity, and TotalAmount.

Now follow the below steps:

1. Open Power BI Desktop, then go to Power Query Editor.

2. Go to the Home tab, then expand New Source and click on Excel Workbook.

3. Select your required Excel file, Then click Open.

4. In the Navigator window, check the orders table, then click OK.

5. Then, in the Add Column tab, click Custom Column.

6. When the custom column window opens, provide the column name and use the following formula.

Duration.Days(Date.From(DateTime.LocalNow()) - [OrderDate])

Where:

• Duration = This tells us we’re going to calculate the amount of time between two dates.
• Days() = This part specifically tells us we’re interested in the number of days in that duration.
• Date.From() = This converts a date and time into a date, removing the time part.
• DateTime.LocalNow() = This gives us the current date and time according to the local time zone of the system.
• [OrderDate] = This represents a specific date, likely when an order was made.

7. Then, you can see a new column added in the Power Query editor.

Power Query Date Functions

Power Query Date functions like Date.AddDays, Date.AddMonths, Date.AddQuarters, etc., creates and manipulates the date component of date, datetime, and datetimezone values. You can read more here: Date functions

Conclusion

In this tutorial, we figured out how to find today’s date in Power Query. We also learned how to take away one day and seven days from today’s date in Power BI with Power Query. Plus, we learned how to subtract a month or a year from a date in Power Query.

Lastly, we learned how to work out the number of days between two dates and how many days there are from today, all using Power Query in Power BI.

Also, you may like:

• Power Query if the date is less than the specific date
• Power BI Split Column by Text Contains
• Power BI Date Slicer
• Power BI Power Query Examples
• Power Query Create Table in Power BI
• Switch in Power BI
• Power BI If Statement