Forum Discussion

trevb's avatar
trevb
Icon for Advocate II rankAdvocate 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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
trevb
Icon for Advocate II rankAdvocate II
10 years ago
  • trevb's avatar
    trevb
    Icon for Advocate II rankAdvocate II
    10 years ago

    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's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      10 years ago

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