Forum Discussion
Creating a custom financial year column to existing query
Hi,
Thanks for taking time to read this. I am new to power query and require some help with creating/adding a custom financial year column to an existing query.
The logic I would like to apply is financial year starts in July if week number is 1, i.e. if we are already in July and the week number is still 52 or 53, assign new financial year when week 1 appears. In the below case 2022/23 does not begin until 04/07/2022 and 2021/22 does not end until week 53 ends (3rd July 2022). (FYI my week begins on Monday)
| Date | Week Number | Month-Year | Financial Year |
| 27/06/2022 | 53 | Jun-22 | 2021/22 |
| 28/06/2022 | 53 | Jun-22 | 2021/22 |
| 29/06/2022 | 53 | Jun-22 | 2021/22 |
| 30/06/2022 | 53 | Jun-22 | 2021/22 |
| 01/07/2022 | 53 | Jul-22 | 2021/22 |
| 02/07/2022 | 53 | Jul-22 | 2021/22 |
| 03/07/2022 | 53 | Jul-22 | 2021/22 |
| 04/07/2022 | 1 | Jul-22 | 2022/23 |
I am using the below in my query so far
let
StartDate = #date (2019,7,1),
EndDate = #date (2024,12,31),
ListOfDates = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
DurationDays = Duration.Days (EndDate - StartDate) + 1,
TableOfDates = Table.FromList(ListOfDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
DateColText = Table.RenameColumns(TableOfDates,{{"Column1", "Date"}}),
Date = Table.TransformColumnTypes(DateColText,{{"Date", type date}}),
DayOfWeek = Table.AddColumn(Date, "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
WeekNumber = Table.AddColumn(DayOfWeek, "WeekNumber", each Number.IntegerDivide(Duration.Days([Date]- #date(Date.Year( Date.AddDays([Date], 188-[Day of Week]))-1,6,20))-[Day of Week],7))
in
WeekNumber
Many thanks
You need to use following formula against a date to get FY
= if [Date]>=Date.From(Number.RoundUp((Number.From(#date(Date.Year([Date]),6,28))-2)/7,0)*7+2) then Text.From(Date.Year([Date]))&"/"&Text.End(Text.From(Date.Year([Date])+1),2) else Text.From(Date.Year([Date])-1)&"/"&Text.End(Text.From(Date.Year([Date])),2)See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLW9SrN0zUyMDJSitWJVjLU9UoEcw3BXCMzXd/ESiS+oa5jQRGQb2gJ5hsbQLVD+UDtpTkg5QYQ5RYw06F8S1Q+XDuUbwLTboSm3AjVdCA3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "FY", each if [Date]>=Date.From(Number.RoundUp((Number.From(#date(Date.Year([Date]),6,28))-2)/7,0)*7+2) then Text.From(Date.Year([Date]))&"/"&Text.End(Text.From(Date.Year([Date])+1),2) else Text.From(Date.Year([Date])-1)&"/"&Text.End(Text.From(Date.Year([Date])),2)) in #"Added Custom"
4 Replies
Before I give the solution, I want to understand whether first Monday of July month is start of your financial year i.e. following are start dates for financial years
7/1/2019 7/6/2020 7/5/2021 7/4/2022 Hi Vijay_A_Verma thank you very much for your help
So sorry my explanation was not correct in the prior post - please see below start date of each FY, the prior FY will then finish 1 day before so for example 2019/20 FY will finish on 06/28/2020. The new Financial year can start in June so long that the majority of the days i.e. 4 days or more sit in July. Again using 2020/21 as an example, the financial year starts on 29th June as it is a Monday however majority of the days in the week (5 out of 7) sit in July
07/01/2019 2019 06/29/2020 2020 06/28/2021 2021 07/04/2022 2022 07/03/2023 2023 07/01/2024 2024 Thanks for your help
You need to use following formula against a date to get FY
= if [Date]>=Date.From(Number.RoundUp((Number.From(#date(Date.Year([Date]),6,28))-2)/7,0)*7+2) then Text.From(Date.Year([Date]))&"/"&Text.End(Text.From(Date.Year([Date])+1),2) else Text.From(Date.Year([Date])-1)&"/"&Text.End(Text.From(Date.Year([Date])),2)See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLW9SrN0zUyMDJSitWJVjLU9UoEcw3BXCMzXd/ESiS+oa5jQRGQb2gJ5hsbQLVD+UDtpTkg5QYQ5RYw06F8S1Q+XDuUbwLTboSm3AjVdCA3FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "FY", each if [Date]>=Date.From(Number.RoundUp((Number.From(#date(Date.Year([Date]),6,28))-2)/7,0)*7+2) then Text.From(Date.Year([Date]))&"/"&Text.End(Text.From(Date.Year([Date])+1),2) else Text.From(Date.Year([Date])-1)&"/"&Text.End(Text.From(Date.Year([Date])),2)) in #"Added Custom"
