Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
How do I calculate the difference between two dates in Power Query M code (not DAX)? I'm looking for the number of days.
Solved! Go to 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])
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Source Data
Replacing null by 0 in sctotal and invvalue columns
Added new SCDate Column
Added new INVDate column
Added list of days or calendar column
Added final value per day column
Expanded the calendar and loaded it.
Use the calendar in slicer and final value column in card.
If your expectation is met the pls accept the solution.
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.
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>)
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
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
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
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
85 | |
85 | |
67 | |
49 |
User | Count |
---|---|
131 | |
113 | |
100 | |
68 | |
67 |