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.

power query current date in power BI

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

power query todays date in Power BI

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.
Power BI date function in power query

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

date formula in power query power BI

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.

power query today minus date in Power BI

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

power query todays date in Power BI

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.
power BI datetime.localnow() minus days

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

power query date minus 1 day

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:

See also  How to Remove Blank From Power BI Slicer?

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.
Power Query Today Minus 7 days

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.

power query date minus 1 month

Now follow the below steps:

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

power query todays date minus 1 month

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

power query date minus one month

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.
power query greater than date in Power BI

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.

power query date - 1 month

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.

date function in power query

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

See also  How to add a column with a dropdown list in Power Query

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

date.year power query in Power BI

2. Under the Home tab, click Enter Data.

today power query formula in Power BI

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

power query today date formula in Power BI

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.”

Power Bi power query greater than date

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

Power BI m query date

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.
Power Query Date Minus 1 Year in Power BI

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

Power Query Date 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:

Power query date between

Follow the below steps to do this:

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

Power BI excel power query date

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

Power BI m query date

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.
Power Query Get all dates between the Start and End date

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

Power BI List Dates Between using Power Query

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.

See also  username() and userprincipalname() in Power BI

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

m query date in Power BI

Now follow the below steps:

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

Power query date from week number

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

Power query get date from date time

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

power query date in Power BI

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

Power BI today power query

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

powerquery today in Power BI

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.
power bi custom column today minus date

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

today date power query in Power BI

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:

  • Hi Bijay, Thank you so very much. This is super helpful. Can you please provide on how to calculate last business day of the month

  • >