Number of Months between two dates

[trevb]

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.

[Bhautik]

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

[Anonymous]

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

[StephLef]

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

[drmbrklyn]

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.

[drmbrklyn]

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)

[AverageAsker]

Will not this

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

work for you?

[trevb]

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 😉

[aaronsteers]

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)

[Greg_Deckler]

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

[Greg_Deckler]

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"

[trevb]

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.

[Greg_Deckler]

@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".

[PaulAlford]

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&q... 

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.

[trevb]

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.

[Greg_Deckler]

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.

[noesk-fl]

How do you have fixed the problem in middle row?

[Greg_Deckler]

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.