Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Avlies
Frequent Visitor

Power Query Date.AddMonths not working

Hi, 

 

I'm struggling to make a date table which includes a column that has the date 6 months before. 

I did it in Dax, but for the measures that follow after I need to make a comparison which isnt accepted in DirectQuery. 

So I thought to do the comparison of dates in Power Query.

 

I have a table with a date column called "Date". I press the fX button in the top and enter this function

= Date.AddMonths([Date],-6)

It gives me an error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?

 

How do I refer to the date column? 

 

It's probably really easy but I'm not used to Power Query 😞 

1 ACCEPTED SOLUTION
PhilipTreacy
Super User
Super User

Hi @Avlies 

 

Download sample PBIX file

 

That fx button is to invoke a custom function that you've already written, not to call inbuilt functions.

To deduct 6 months from your date column just click the Custom Column button

addcc.png

 

and add this in the code box

addcc1.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

6 REPLIES 6
PhilipTreacy
Super User
Super User

Hi @Avlies 

 

Download sample PBIX file

 

That fx button is to invoke a custom function that you've already written, not to call inbuilt functions.

To deduct 6 months from your date column just click the Custom Column button

addcc.png

 

and add this in the code box

addcc1.png

 

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Thank you! This worked for my Date table. Not for the original DirectQuery table. But I'll just add a relationship to it then 🙂 

amitchandak
Super User
Super User

@Avlies , The syntax seems correct for power query

Date.AddMonths([Date],-6) //if Date is date data type

https://www.youtube.com/watch?v=S_meG32rDio

 

in case datetime try

Date.AddMonths(DateTime.Date([Date]),-6)

 

But not all functions work in Direct Query. But that should give import mode warning option

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak Thank you for your fast response. It was a DateTime field. But I still get the same error. 

Do I need to define the field shorthand somewhere before it works?

@Avlies , I tried on Direct Query

 

Date.AddMonths(DateTime.Date([Date]),-1) is not supported in Direct Query

 

But I was able to do Date.AddMonths([Sales Date],-1) //but my date to not have time

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak Thank you for your help. I don't think it will work in directquery. I've made a new date table to which I could add it. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.