Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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 @Avlies
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 @Avlies
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 🙂
@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
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
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 |
---|---|
84 | |
76 | |
72 | |
48 | |
39 |
User | Count |
---|---|
111 | |
56 | |
51 | |
42 | |
42 |