Forum Discussion
trevb
10 years agoAdvocate II
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 ...
Greg_Deckler
10 years agoCommunity Champion
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
7 years agoNew Member
How do you have fixed the problem in middle row?