Forum Discussion
Create Fiscal Year Offset calculation in Power Query Custom Date Table
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.
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])
3 Replies
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])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 0That 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
