Forum Discussion
Custom column producing an error - calculate previous year based on month
Hi
Can anyone please tell me why the following custom column results in an error (I've created it in the advanced editior screen as i need to create the variables:
ThisYear = Date.Year(DateTime.LocalNow),
ThisMonth = Date.Month(DateTime.LocalNow),
#"Added Last3Years" = Table.AddColumn(#"Renamed Columns", "Last3Years", each if ThisMonth < 4 then ThisYear - 4 else ThisYear - 3),
Cheers
Jim
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:
- Select TransactionDate
- Go to Add Column → Date → Year → Year
- Then again: Add Column → Date → Month → Month
- Go to Add Column → Custom Column
• 5. Use this formula:
if [Month] < 4 then [Year] - 4 else [Year] - 3That’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.
8 Replies
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:
- Select TransactionDate
- Go to Add Column → Date → Year → Year
- Then again: Add Column → Date → Month → Month
- Go to Add Column → Custom Column
• 5. Use this formula:
if [Month] < 4 then [Year] - 4 else [Year] - 3That’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.Jim,
If you place brackets after "LocalNow" your code will work fine, e.g.
ThisYear = Date.Year(DateTime.LocalNow()),Hope this helps
Hello jimbob2285,
Thank you for reaching out to the Microsoft Fabric Community Forum.
I have reproduced your scenario in Power BI Desktop using a sample dataset, and I was able to get the expected results as per your requirement.Here’s what I did:
- Created a small sample sales table with Date, Customer, Sales.
- In Power Query, I added a custom column with your logic:
let ThisYear = Date.Year(DateTime.LocalNow()), ThisMonth = Date.Month(DateTime.LocalNow()) in if ThisMonth < 4 then ThisYear - 4 else ThisYear - 3- Loaded the data back into the model.
- Validated using a Table and a Matrix visual in Power BI.
Since today is Aug 2025, ThisYear = 2025 and ThisMonth = 8.
As per your logic:- Because 8 >= 4, the calculation returns ThisYear - 3 = 2022.
- So, every row in the Last3Years column shows 2022, which matches your requirement.
Here’s a quick snapshot of the expected output I got:
For your reference, I’ve attached the .pbix file I used to test this. You can download and open it to check the query steps and visuals.
Hope this helps. Please let me know if you face any issues while implementing in your own report.
Best Regards,
Ganesh Singamshetty.Hello jimbob2285,
Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.
Thank you.
Your error happens because ThisYear and ThisMonth must be defined before the Table.AddColumn.
let
Source = #"Renamed Columns",
ThisYear = Date.Year(DateTime.LocalNow()),
ThisMonth = Date.Month(DateTime.LocalNow()),
AddedLast3Years = Table.AddColumn(
Source,
"Last3Years",
each if ThisMonth < 4 then ThisYear - 4 else ThisYear - 3
)
in
AddedLast3Years
If you want row-based logic, use the row’s date column instead of DateTime.LocalNow().Hello jimbob2285,
We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.
Thank you.
Hello jimbob2285,
Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Thank you for being part of the Microsoft Fabric Community.
