Forum Discussion
Number of Months between two dates
This seems like a really dumb thing to be asking.
I need to count the number of Months between two dates. I can happily find out the number of days, but that doesn't help much as number of days in a month varies. This can easily be done in Excel using DateDiff but I cannot figure out how to do this in PowerQuery. I need that figure to move to the next step of my query.
The only other thing that might do this is if I could get a count of a group of rows.
I'd enjoy figuring t this out for myself if I had the time but I need to get this done quickly so any hints gratefully received.
18 Replies
It is the same function in DAX as Excel. Create a measure like:
Months = DATEDIFF([start],[end],MONTH)
https://msdn.microsoft.com/en-us/library/dn802538.aspx
EDIT: Hang on, just caught the reference to PowerQuery, give me a minute.
I use this formula in a calucated column of my date table:
OrdMonth = if(today()<CAL[Date],datediff(today(),CAL[Date],MONTH),datediff(CAL[Date],today(),MONTH)*-1)
It returns:
0 if the date is in the current month
-1 if the date is in the previous month
+1 if the dates in the next month
Its' great to filter for windows of time (last three months, within 3 months before and after)
It's easy to adapt for use with Years, Days or Weeks.
You can also swap out the "Today" argument for a different, specific date.
This also solves the issue with datediff where the start date cannot be after the end date.
A work in progress, but maybe will help some:
let Source = Csv.Document(File.Contents("C:\temp\powerbi\months.csv"),[Delimiter=",", Encoding=1252]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"start", type date}, {"end", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Years", each Number.RoundDown(Duration.Days(([end] - [start]) / 365.25),0)), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Days Left", each Number.Mod(Duration.Days(([end] - [start])),365.25)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "StartMonth", each Date.Month([start])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndMonth", each Date.Month([end])), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Months", each [Years]*12 + ([EndMonth] - [StartMonth])) in #"Added Custom4"startendYearsDays LeftStartMonthEndMonthMonths
1/1/2015 1/29/2016 1 27.75 1 1 12 1/29/2015 1/6/2016 0 342 1 1 0 6/12/2014 7/2/2016 2 20.5 6 7 25 Need to fix that middle row obviously.
How do you have fixed the problem in middle row?
That data table should look like:
start end Years Days Left StartMonth EndMonth Months
1/1/2015 1/29/2016 1 27.75 1 1 12
1/29/2015 1/6/2016 0 342 1 1 0
6/12/2014 7/2/2016 2 20.5 6 7 25
This gives me the correct answer for all of the rows, but I know there is a boundary case I am probably not accounting for:
let Source = Csv.Document(File.Contents("C:\temp\powerbi\months.csv"),[Delimiter=",", Encoding=1252]), #"Promoted Headers" = Table.PromoteHeaders(Source), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"start", type date}, {"end", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Years", each ([end] - [start]) / 365.25), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Years", Int64.Type}}), #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Days Left", each Number.Mod(Duration.Days(([end] - [start])),365.25)), #"Added Custom2" = Table.AddColumn(#"Added Custom1", "StartMonth", each Date.Month([start])), #"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndMonth", each Date.Month([end])), #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Months", each [Years]*12 + ([EndMonth] - [StartMonth])) in #"Added Custom4"
Will not this
Date.Month([DateTime1]) - Date.Month([DateTime2])
work for you?
I should have done an update on this. What I went with was
((Date.Year([EndDate])-Date.Year([StartDate]))*12) + Date.Month([EndDate]) - Date.Month([StartDate])
This works fine for me and matches the results I was looking to achieve. I sped my exisiting query up by an order of magnitude :-)
Still really wish there was a DateDiff equivalent built into the query language though ;-)
I had a similar question, but for my case, I want the formula to be sensitive to the day of month as well as the month of the year. For this, I added an offset of -1 to your formula above any time the day of the end date is less than the day of the start date.
So number of months between Jan 15 and Feb 10 is "0", while number of months between Jan 15 and Feb 20 is "1".
(12*(Date.Year([EndDate])-Date.Year([StartDate])))
+ (Date.Month([EndDate]) - Date.Month([StartDate]))
+ (if Date.Day([EndDate]) < Date.Day([StartDate]) then -1 else 0)
I use this expression to evaluate a list of dates relative to today.
It returns 0 if the date is within the current month
-1 if the date is in the preceding month
+1 if the date is in next month.
Can easily be adapted for other uses (Day, Week, Quarter).
OrdMonth = if(today()<CAL[Date],datediff(today(),CAL[Date],MONTH),datediff(CAL[Date],today(),MONTH)*-1)
Hello,
This, although it's not really elegant, does the job to get the (rounded) number of months between two dates:
Number.Round(Number.From([End Date]) - Number.From([Begin Date]) / 30.4, 0)
Cheers
I have created following function to cover all possbile scenerio. it will give the accurate result.
Function Body:
= (StartDate as datetime,EndDate as datetime) => let Source = {Number.From(#date(Date.Year(StartDate),Date.Month(StartDate),Date.Day(StartDate)))..Number.From(#date(Date.Year(EndDate),Date.Month(EndDate),Date.Day(EndDate)))}, #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Month Year", each Date.ToText([Column1],"MMM-yyyy")), #"Grouped Rows" = Table.Group(#"Added Custom", {"Month Year"}, {{"Count", each Table.RowCount(_), Int64.Type}}), #"Counted Rows" = Table.RowCount(#"Grouped Rows") in #"Counted Rows"Invoked example:
let Source = getTotalMonths(#datetime(2020, 11, 11, 0, 0, 0), #datetime(2023, 07, 11, 0, 0, 0)) in Source
