Calculate difference between two dates power query

[jshutters]

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

[Rickmaurinus]

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]

Hello 

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

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

[jshutters]

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]

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.

[storgall]

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]

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

[dmatliak]

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]

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

[LivioLanzo · Accepted solution]

Hi @jshutters

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

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

[jacekcholewa]

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?