Forum Discussion

amandabus21's avatar
amandabus21
Helper V
3 years ago
Solved

Fiscal Year / Calendar Year

Hi,    I need help creating a new column in Power Query where I create a Calendar Year.    Our fiscal year begins in July.      Id like a column named "Calendar Year" Thanks!
Need Help
  • ppm1's avatar
    ppm1
    3 years ago

    You can create a custom column with this expression.

     

    = if Date.Month([Merged])>6 then Date.Year([Merged]) + 1 else Date.Year([Merged])

     

    Pat

KT_Bsmart2gethe's avatar
KT_Bsmart2gethe
Impactful Individual
3 years ago

Hi amandabus21 ,

 

Create a blank query, go to the advanced editor, delete everything in the editor, and then paste the code below:

let
    startDate = #date(2020, 1, 1),                                      //start date. update the date if you need it
    endDate = Date.From(DateTime.LocalNow()),                           //Today date
    numMonths = Duration.Days(endDate - startDate),                     //get number of months
    dateList = List.Dates(startDate, numMonths, #duration(1,0,0,0)),    //generate a list of dates
    generateCalendar = List.Transform(
                            dateList, 
                            each 
                            {                                           
                                    _,                                  //date
                                    Date.Day(_),                        //get day
                                    Date.Month(_),                      //get month
                                    Date.Year(_),                       //get year
                                    Date.DayOfWeekName(_),              //get day of week name
                                    Date.MonthName(_),                  //get month name    
                                    (                                   //get financial period
                                        if Date.Month(_)-6 < 1 
                                        then Date.Month(_)+6 
                                        else Date.Month(_)-6
                                    ), 
                                    (                                   //get financial year
                                        if _ > #date(Date.Year(_),6,30) 
                                        then "FY"&Date.ToText(_,"yyyy")&"-"&Date.ToText(Date.AddYears(_,1),"yy") 
                                        else "FY"&Date.ToText(Date.AddYears(_,-1),"yyyy")&"-"&Date.ToText(_,"yy")
                                    )
                                }
                        ),
    output = Table.FromRows(
                    generateCalendar, 
                    {                                                   //column name: same order as above
                        "Date",                                         
                        "Day", 
                        "Month", 
                        "Year", 
                        "Day Of Week", 
                        "Month Name", 
                        "Financial Period", 
                        "Financial Year"
                    }
                )
in
    output

 

Regards

KT