Period table according to financial year

[Anonymous]

I have created a period table for a slicer and would like to use data according to my financial year.  My financial year month is 7 and below is the table power query.

Can someone please advise what field I should change to get the 1Y, 2Y and 3Y data according to the Financial year not the calendar year?

let


TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),

Ranges = {

{"All Dates",MinDates,MaxDates, 1},
{"6M", Date.AddMonths(TodaysDate, -6), TodaysDate, 3},
{"13M", Date.AddMonths(TodaysDate, -13), TodaysDate, 4},
{"YTD", Date.From(Date.StartOfYear(TodaysDate)), TodaysDate, 5},
{"1Y", Date.AddYears(TodaysDate, -1), TodaysDate, 6},
{"2Y", Date.AddYears(TodaysDate, -2), TodaysDate, 7},
{"5Y", Date.AddYears(TodaysDate, -5), TodaysDate, 8}
},
fxCreatePeriodTabe = ( PeriodName as text, StartDate as date, EndDate as date, SortOrder as number ) as table =>
let
DayCount = Duration.Days(EndDate-StartDate)+1,
DateList = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddPeriodName = List.Transform(DateList, each {PeriodName,_,SortOrder}),
CreateTable = #table( type table[Period=text, Date=date, Sort=number], AddPeriodName)
in
CreateTable,

GetTables = List.Transform(Ranges, each fxCreatePeriodTabe(_{0}, _{1}, _{2}, _{3})),
Output = Table.Combine(GetTables)
in
Output

[Anonymous]

I agree with you as I need a mix of periods to see the result for the last 6 or 13 months with YTD, 1Y, 2Y and 3Y as a Financial year.

[lbendlin]

You are basing your ranges off of "TodaysDate" so it is irrelevant when your fiscal year starts. Maybe you want to redefine what 1Y, 2Y and 3 (5?) Y means for you.

The only range that seems to be impacted is "YTD".  There you need to change Date.StartOfYear  to point to July instead of January.