Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.