Custom column producing an error - calculate previous year based on month

hello Jim,

the problem you have is because of Datetime.LocalNow. this is a fuction, if you refer them, you should use (). change your code to below: 
ThisYear = Date.Year(DateTime.LocalNow()),
ThisMonth = Date.Month(DateTime.LocalNow()),
this should help.

regards

Marco

Hi jimbob2285 

You're on the right track with defining ThisYear and ThisMonth in the Advanced Editor, but the error you're seeing is due to variable scoping in Power Query (M language).

In Power Query, variables like ThisYear and ThisMonth defined outside the each expression aren't automatically accessible inside it. That’s why your custom column throws an error — it doesn’t recognize those variables inside the each scope.

The error happens because Power Query doesn’t automatically pass variables like ThisYear and
ThisMonth into the each expression — they’re scoped outside and not recognized inside the custom column logic.

You’ve got two solid options to solve this:

✅Option 1: Use M Code in the Advanced Editor

Here’s what worked for me using Excel as the source:

let
  Source = Excel.Workbook(File.Contents("/Users/olufemiolamoyegun/Desktop/Power Query.xlsx"), null, true),
  #"Navigation 1" = Source{[Item = "Table1", Kind = "Table"]}[Data],
  #"Changed column type" = Table.TransformColumnTypes(#"Navigation 1", {{"Name", type text}, {"TransactionDate", type date}}),
  #"Inserted year" = Table.AddColumn(#"Changed column type", "Year", each Date.Year([TransactionDate]), type nullable number),
  #"Inserted month" = Table.AddColumn(#"Inserted year", "Month", each Date.Month([TransactionDate]), type nullable number),
  #"Added custom" = Table.AddColumn(#"Inserted month", "Custom", each if [Month] < 4 then [Year] - 4 else [Year] - 3)

this creates a dynamic column that subtracts either 4 or 3 from the year depending on the month in each row’s TransactionDate

Option 2: No Code — Just Use Power Query UI

If you prefer working through the interface:

1. Select TransactionDate
2. Go to Add Column → Date → Year → Year
3. Then again: Add Column → Date → Month → Month
4. Go to Add Column → Custom Column

• 5. Use this formula:

if [Month] < 4 then [Year] - 4 else [Year] - 3

That’s it — no errors, no fuss. Works great for fiscal calendars or any logic based on month thresholds. 

Hope this helps someone else out there!

I built this in Excel 365, but the same logic applies in Power BI Desktop too.