cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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])

Proud to be a Datanaut!

10 REPLIES 10
Helper V

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

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

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

Read in-depth articles? -> 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

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

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])

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?

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.