Fiscal Year / Calendar Year

[amandabus21]

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!

[KT_Bsmart2gethe]

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

[ppm1 · Accepted solution]

You can create a custom column with this expression.

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

Pat

[Stuart909]

How would I change this to be the first monday in april? 

[amandabus21]

Hi, @ppm1 

I ran into an issue with this.

The calendar year should be "2022" not 2024. 

Thank you for your help!

[amandabus21]

@ppm1 was able to fix with "-1" instead of "+1" thank you!