Power Query Date.AddMonths not working

[Avlies]

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 😞 

[PhilipTreacy · Accepted solution]

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

and add this in the code box

Regards

Phil

[Avlies]

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]

@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

[Avlies]

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?

[amitchandak]

@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

[Avlies]

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.