cancel
Showing results for
Search instead for
Did you mean:
Resolver I

## Calculate difference between two dates power query

How do I calculate the difference between two dates in Power Query M code (not DAX)?  I'm looking for the number of days.

1 ACCEPTED SOLUTION
Solution Sage

you need to use the duration functions: https://docs.microsoft.com/en-us/powerquery-m/duration-days

=each Duration.Days([Date1] - [Date2])

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

10 REPLIES 10
Resolver II

jshutters,

Good question. Your question depends on the following:

1. Do you need the number of days between 2 dates?

Use Duration.Days ( [EndDate] - [StartDate] ) +1
2. Do you need the number of weekdays between 2 dates?

Generate a list of dates between start and enddate, and remove the weekends. Count the number of days left in your list.

3. Do you need working days between 2 dates?

Generate a list of dates between start and enddate, remove weekends and holidays. Then count the number of days left in your list.

For 2 and 3 I'll just drop this blog here so you can read the details. It's a bit more complex but replicates the DATEDIFF function for the M language:

https://gorilla.bi/power-query/working-days-between-dates/

Cheers!

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

New Member

Hello

How do I calculate the years between 2 dates on power query M coding?

Date formate is e.g. 2022-12-10

Resolver I

J2008F, to calculate the years between 2 dates, you can use the Date.Year function in M coding.  For example, Date.Year(<insert name of column2>) - Date.Year(<insert name of column1>)

New Member

A further queston on Duration.Days, the formula Duration.Days([Date1] - [Date2]) did not cater both date inclusive situation. Is there any way to modify the formula for both date inclusve situation?

Example, The Duration.Days formula gives 6 for days between 2022/02/22 and 2022/02/28, but it should be 7 if both date inclusive.

Frequent Visitor

Just add one day to the first date: Duration.Days(Date.AddDays([Date1],1) - [Date2])

Or just add one to the result: Duration.Days([Date1] - [Date2])+1

Advocate I

One note: I kept receiving errors on the [Date1] - [Date2] until I explicitly converted my date values to a date using Date.From(): e.g. (for an aging calculation)

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

Frequent Visitor

Hello is it possible to calculate number of days using date that I specify. I need to calculate number of day between August 31, 2021 and some Due date I have in data. When I use this formula Duration.Days(Date.From(2021-08-31) - Date.From([DEB_DAT])), it returns strange result.

Thanks for help

Resolver II

Hi, you can find the solution in this post.

http://blog.iwco.co/2018/12/07/numero-de-dias-entre-dos-fechas-power-query-m/

Regards

John Bocachica
Senior BI Consultant
Information Workers
http://www.iwco.co
Solution Sage

you need to use the duration functions: https://docs.microsoft.com/en-us/powerquery-m/duration-days

=each Duration.Days([Date1] - [Date2])

Did I answer your question correctly? Mark my answer as a solution!

Proud to be a Datanaut!

Regular Visitor

In similar cases is better to use DAX or PowerQuery to this calculations?

Can anyone write advantages and disadventages this two ways of solving similar cases?

## Helpful resources

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors