Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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 😞
Solved! Go to Solution.
Hi @Anonymous
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
Proud to be a Super User!
Hi @Anonymous
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
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 🙂
@Anonymous , 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
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?
@Anonymous , 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
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.
User | Count |
---|---|
63 | |
59 | |
56 | |
38 | |
29 |
User | Count |
---|---|
82 | |
62 | |
45 | |
41 | |
40 |