Results 1 to 7 of 7

Thread: Power Query to Get Fiscal Year & Fiscal Month
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,062
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Power Query to Get Fiscal Year & Fiscal Month

    My fiscal year is 10/1 - 9/30 and I would like to modify the following M code so that it reflect the fiscal month and year correctly. Can someone help? So October 2018 should be fiscal month 1, January 2019 should be fiscal month 4, July 2019 should be fiscal month 10, etc.


    Code:
    let
       
    
        EndFiscalYearMonth = 9,   //set this as the last month number of your fiscal year : June = 6, July =7 etc
    
    
        StartDate= #date(2018, 10, 1),     // Change start date  #date(yyyy,m,d)   
        EndDate = DateTime.LocalNow(),  // Could change to #date(yyyy,m,d) if you need to specify future date
    
    
    /* Comment out the above StartDate and EndDate using // if you want to use a dynamic start and end date based on other query/table
       You will need to change "Sales" and "Invoice Date" in 2 lines below and then remove the // 
    */
    
    
        //TableName = Sales    
        //DateColumnName = "Invoice Date"
        //StartDate = Record.Field (   Table.Min(TableName,DateColumnName)  ,DateColumnName), 
        //EndDate = Record.Field(Table.Max(TableName,DateColumnName),DateColumnName),
    
    
        
        DateList = List.Dates(StartDate, Number.From(EndDate)- Number.From(StartDate)+1 ,#duration(1,0,0,0)),
    
        #"Converted to Table" = Table.FromList(DateList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Named as Date" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Named as Date",{{"Date", type date}}),
        #"Inserted Year" = Table.AddColumn(#"Changed Type", "Calendar Year", each Date.Year([Date]), type number),
        #"Inserted Month Number" = Table.AddColumn(#"Inserted Year", "Month Number", each Date.Month([Date]), type number),
        #"Long Month Name" = Table.AddColumn(#"Inserted Month Number", "Month Long", each Date.MonthName([Date]), type text),
        #"Short Month Name" = Table.AddColumn(#"Long Month Name", "Month", each Text.Start([Month Long], 3), type text),
        #"Fiscal Month Number" = Table.AddColumn(#"Short Month Name", "Fiscal Month Number", each if [Month Number] > EndFiscalYearMonth  then [Month Number]-EndFiscalYearMonth  else [Month Number]+EndFiscalYearMonth),
        #"Changed Type1" = Table.TransformColumnTypes(#"Fiscal Month Number",{{"Fiscal Month Number", Int64.Type}}),
        #"Fiscal Year" = Table.AddColumn(#"Changed Type1", "Fiscal Year", each if [Fiscal Month Number] <=EndFiscalYearMonth  then [Calendar Year]+1 else [Calendar Year]),
        #"Changed Years to Text" = Table.TransformColumnTypes(#"Fiscal Year",{{"Fiscal Year", type text}, {"Calendar Year", type text}}),
        FYName = Table.AddColumn(#"Changed Years to Text", "FYName", each "FY"&Text.End([Fiscal Year],2))
    in
        FYName

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query to Get Fiscal Year & Fiscal Month

    maybe it will help: Build Calendar
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,062
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query to Get Fiscal Year & Fiscal Month

    Thanks but that doesn't show how to get the fiscal month and fiscal year.

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query to Get Fiscal Year & Fiscal Month

    there is Fin Year and Fin Month (Financial = Fiscal)
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  5. #5
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,062
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query to Get Fiscal Year & Fiscal Month

    Quote Originally Posted by sandy666 View Post
    there is Fin Year and Fin Month (Financial = Fiscal)
    Ah! Didn't read that. I got the fiscal year correct but I'm having trouble with the following part to get the correct fiscal month


    Code:
    = Table.AddColumn(#"Inserted Day Name", "Fiscal Month", each if Date.Month([Date]) >=10 then Date.Month([Date])-9 else Date.Month([Date])+9)

    My fiscal month starts Oct and ends in Sept, thus it should result in the following:
    Oct - 1
    Nov - 2
    Dec - 3
    Jan - 4
    Feb - 5
    ....
    ....
    Sept - 12

  6. #6
    Board Regular
    Join Date
    Jun 2014
    Posts
    1,062
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Power Query to Get Fiscal Year & Fiscal Month

    Was able to figure it out by changing it to the following. Thanks again!

    = Table.AddColumn(#"Inserted Day Name", "Fiscal Month", each if Date.Month([Date]) >=10 then Date.Month([Date])-9 else Date.Month([Date])+3)

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,027
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Power Query to Get Fiscal Year & Fiscal Month

    you are welcome and have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •