Forum Discussion

trevb's avatar
trevb
Advocate II
10 years ago

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 ...
Need Help
Bhautik's avatar
Bhautik
New Member
5 years ago

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