Skip to main content
cancel
Showing results for 
Search instead 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

Reply
jshutters
Resolver I
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
LivioLanzo
Solution Sage
Solution Sage

Hi @jshutters

 

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!  

View solution in original post

10 REPLIES 10
Rickmaurinus
Helper V
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

 

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

 

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

J2008F
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

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

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

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

rfratto
Advocate I
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]))

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

jbocachica
Resolver II
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
LivioLanzo
Solution Sage
Solution Sage

Hi @jshutters

 

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!  

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
Fabric Community Conference

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.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

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

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.