Create Fiscal Year Offset calculation in Power Query Custom Date Table

[TRK]

Hi,

I have a custom calendar created in Power Query - M.  

I want to be able to flag the current FISCAL year  (my fiscal runs April 1 - March 31st) - so I need to be able to calculate the Fiscal Year Offset to do that.

See Query table extract below: (note:  I simplified this table to export here - the date column actually has every day in the month (not just the first day)) 

I was able to create a columns to calculate "Current Year offset" and then from that I can flag the current year as the value is 0.

I also have Fiscal month # column, Fiscal quarter, etc.  But I want to be able to flag the current FISCAL year  (my fiscal runs April 1 - March 31st)
The code in custom columns I have already that are working as expected:

FiscalYearEndMonth = 3   

 "FiscalMonthNum"

 if [MonthNum] > FiscalYearEndMonth  then [MonthNum] - FiscalYearEndMonth  else [MonthNum] + (12 - FiscalYearEndMonth)

 "CurYearOffset"

 Date.Year([Date]) - Date.Year(CurrentDate)

 "Flag Current Calendar Year" (cond. Column)

 if [CurYearOffset] = 0 then "Current Year" else null

Date	FiscalMonthNum	Fiscal Year	CurMonthOffset	CurYearOffset	Flag Current Calendar Year	What I want: Offset FY	What I want:    FY Flag
2021-04-01	1	2021-2022	-11	-1	null	0	Current FY
2021-05-01	2	2021-2022	-10	-1	null	0	Current FY
2021-06-01	3	2021-2022	-9	-1	null	0	Current FY
2021-07-01	4	2021-2022	-8	-1	null	0	Current FY
2021-08-01	5	2021-2022	-7	-1	null	0	Current FY
2021-09-01	6	2021-2022	-6	-1	null	0	Current FY
2021-10-01	7	2021-2022	-5	-1	null	0	Current FY
2021-11-01	8	2021-2022	-4	-1	null	0	Current FY
2021-12-01	9	2021-2022	-3	-1	null	0	Current FY
2022-01-01	10	2021-2022	-2	0	Current Year	0	Current FY
2022-02-01	11	2021-2022	-1	0	Current Year	0	Current FY
2022-03-01	12	2021-2022	0	0	Current Year	0	Current FY
2022-04-01	1	2022-2023	1	0	Current Year	1
2022-05-01	2	2022-2023	2	0	Current Year	1
2022-06-01	3	2022-2023	3	0	Current Year	1
2022-07-01	4	2022-2023	4	0	Current Year	1
2022-08-01	5	2022-2023	5	0	Current Year	1
2022-09-01	6	2022-2023	6	0	Current Year	1
2022-10-01	7	2022-2023	7	0	Current Year	1
2022-11-01	8	2022-2023	8	0	Current Year	1
2022-12-01	9	2022-2023	9	0	Current Year	1
2023-01-01	10	2022-2023	10	1	null	1
2023-02-01	11	2022-2023	11	1	null	1
2023-03-01	12	2022-2023	12	1	null	1

Any help would be appreciated.

[TRK · Accepted solution]

I found a solution.  

I created the following 3 columns:
FY End Year:  (provides the end year of the fiscal year)

= Table.AddColumn(#"Added FY End", "FY Year", each if [MonthNum] > FiscalYearEndMonth
then [Year] + 1 else [Year])

Year.Now
step 1:  (fills column with current date & time now - all same value)

= Table.AddColumn(#"FY End Year", "Year.Now", each DateTime.LocalNow())
step 2:  (extract year from this column using transform tab in ribbon: button 'Date' and selecting 'year' - to just have dynamic current year) 

= Table.TransformColumns(#"Date.Now (Year.Now)",{{"Year.Now", Date.Year, Int64.Type}})

Current FY Offset (The current fiscal year will have 0's)
= Table.AddColumn(#"Extracted Year", "Current FY Offset", each [FY End Year-[Year.Now])

[amitchandak]

@TRK , Try a new column like

if [Date] >= #date(if Date.Month(Date.From(DateTime.FixedLocalNow())) >3 Then Date.Year(Date.From(DateTime.FixedLocalNow()))-1 else Date.Year(Date.From(DateTime.FixedLocalNow())),4,1) && [Date] >= #date(if Date.Month(Date.From(DateTime.FixedLocalNow())) >3 Then Date.Year(Date.From(DateTime.FixedLocalNow())) else Date.Year(Date.From(DateTime.FixedLocalNow()))+1 ,3,31) then 1 else 0

[TRK]

That doesn't seem to work.  I fixed the 2 Then to then and it didn't accept && so I change that to and  but everything in the column returned 0