topic Re: Number of Months between two dates in Developer

Number of Months between two dates

trevb — Sat, 30 Jan 2016 09:54:35 GMT

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.

Re: Number of Months between two dates

Greg_Deckler — Sat, 30 Jan 2016 12:52:26 GMT

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.

Re: Number of Months between two dates

Greg_Deckler — Sat, 30 Jan 2016 13:25:53 GMT

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.

Re: Number of Months between two dates

Greg_Deckler — Sat, 30 Jan 2016 13:29:41 GMT

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

Re: Number of Months between two dates

Greg_Deckler — Sat, 30 Jan 2016 13:33:20 GMT

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"

Re: Number of Months between two dates

trevb — Sat, 30 Jan 2016 17:56:05 GMT

Re: Number of Months between two dates

trevb — Sat, 30 Jan 2016 18:05:13 GMT

Many thanks for this. Especially 'cos I managed to post it in the wrong part of the forum!

I think I'm going to fail with this anyway. What I was trying to do was to add a row for every month between two dates. I am able to do that and it works fine on my test data so I was working on the rest which included working out what a monthly figure for budget would be, which is why I needed the count above.

I tried something using Group to get a table with a count of the number of rows added using the above and that worked so I kept going. Alas when I replaced the test data with the final data all went Pete Tong. The query fails with a Stack Overflow error presumably because the table it is trying to create is just too big. It does this just adding the rows before I get to the bit where I need the count of months. There are 173 records in the table but honestly I did not think this would be too much as I have previously done this natively in a spreadsheet.

If I can fix the row adding I will try again but using your suggestions rather than generating another table which could be sucking up memory.

Thanks again.

Re: Number of Months between two dates

Greg_Deckler — Sat, 30 Jan 2016 18:26:06 GMT

@trevb - No problem, if you can post some sample, sanitized data and explain what you are ultimately trying to achieve (end result I am still not clear on) then we might be able to come up with a solution. 173 records should be trivial for Power BI to ingest and process. Some problems are trivial in DAX, like Months between dates and we might be able to get to a solution easier doing the necessary data manipulation in DAX and Power BI modeling rather than purely in "M".

Re: Number of Months between two dates

PaulAlford — Sat, 20 Feb 2016 13:41:13 GMT

Just as an aside to this, I would recommend anyone using Power BI as a serious tool to get hold of this book:

http://www.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/073569835X/ref=sr_1_1?ie=UTF8&qid=1455975416&sr=8-1&keywords=definitive+dax

It is certainly not an easy read if you're very new to DAX or Excel formulas, but it has a wealth of information and will answer pretty much every question you might have.

Re: Number of Months between two dates

trevb — Tue, 23 Feb 2016 12:27:11 GMT

Ta Paul

I already have that but thanks for the tip. Sadly this time I needed to be using Power Query just because I am using the information in both PowerBI and Excel and that was the easiest way to bring them together.

Re: Number of Months between two dates

AverageAsker — Thu, 10 Mar 2016 22:14:22 GMT

Will not this

Date.Month([DateTime1]) - Date.Month([DateTime2])

work for you?

Re: Number of Months between two dates

trevb — Fri, 11 Mar 2016 07:40:14 GMT

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 😉

Re: Number of Months between two dates

aaronsteers — Tue, 15 Nov 2016 18:38:45 GMT

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)

Re: Number of Months between two dates

drmbrklyn — Mon, 17 Jul 2017 15:29:15 GMT

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)

Re: Number of Months between two dates

drmbrklyn — Mon, 17 Jul 2017 15:39:52 GMT

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.

Re: Number of Months between two dates

StephLef — Tue, 28 Nov 2017 13:22:20 GMT

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

Re: Number of Months between two dates

noesk-fl — Wed, 10 Apr 2019 07:57:53 GMT

How do you have fixed the problem in middle row?

Re: Number of Months between two dates

Anonymous — Mon, 04 Nov 2019 20:46:35 GMT

To have a Decimal number i use:

Date.Month([EndDate])-Date.Month([StartDate])
+
((Date.Day([EndDate])/Date.DaysInMonth([EndDate])) + (Duration.TotalDays(Date.EndOfMonth([StartDate])-[StartDate])/Date.DaysInMonth([StartDate]))-1))

Re: Number of Months between two dates

Bhautik — Fri, 06 Nov 2020 18:45:27 GMT

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