The easiest way to find the difference between 2 dates in M language is by using Duration.Days(). In this post, we are going to discuss that method first and then how to find the difference between a date and today in detail with examples.
Dataset
Sample retail shop transaction dataset imported to the .pbix file. We are going to work with 2 date columns. order date and order completed date.
Steps
Here’s the formula
Duration.Days(Date 1-Date 2)What Duration.Days() does is simply returning the day element of duration value.
Subtraction of any 2 date values or datetime values would return a duration value and can be used as the argument in this formula.
First open Power query editor. Go to Add Column >> Custom Column and add the function.
Here is how the function is used to calculate duration between order date and order completed date to create a new column Duration with duration between 2 dates in days.
Duration.Days([completed date]-[order date])This works with datetime value as well.
Return Other Time Elements Using Other Duration Functions
There is 4 other durations function you can use to get different elements of duration value from datetime value. The duration value contains 4 elements.
#duration (days, hours, minutes, seconds)
#duration (2, 3, 4, 5)This means when you subtract 2 datetime values, Duration. functions have 4 elements to work with.
- Duration.Days
- Duration.Hours
- Duration.Minutes
- Duration.Seconds
These functions return only one-time element of the duration and not the time conversion.
For example, let’s take a look at the below 2 datetime values.
Note that the first function returns day element (30 days) and the second function returns hour element (0 hours) of the duration between 2 datetime values (#duration (30, 00, 00, 00) / 30 days, 00 hours, 00 minutes, 00 seconds)
The most common use case for calculating date differences is calculating the duration between a date and today.
How To Calculate The Difference Between A Date And Today In Power Query.
This formula is for finding the duration between order date (datetime) and today.
Duration.Days(Date.From(DateTime.LocalNow())-Date.From([order date]))Breakdown
DateTime.LocalNow( ) returns todays date and time in datetime format. order date is already in datetime format.
Date.From( ) fetches date element from datetime value. So, we apply Date.From( ) to both today and order date and subtract order date from today which returns a duration value and apply Duration.Days( ) to it which returns the days element of the duration.