Creating a custom financial year column to existing query

[threw001]

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

[Vijay_A_Verma]

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

[threw001]

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

[Vijay_A_Verma · Accepted solution]

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"

[threw001]

Hi @Vijay_A_Verma 

This has worked perfectly, thank you so much!

Thanks