How to change the day portion of the date to "1".

[Anonymous]

Hi Everyone,

I've a date where I've a range for 2 years and in this range I want to change all the days to 1st of that month. How can I do that I am working with directquery.

Thanks

[selinaz · Accepted solution]

Hi @Anonymous ，

The Direct query connection mode is very restrictive for DAX functions, in another word, most DAX statements may not be supported in this mode, hence your error above. The more recommended method  for you is to write the relevant SQL statements directly when connecting to the data source, to present the data you need precisely to the desktop.

Best regards, 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly .

[tamerj1]

Hi @Anonymous 
You can use

EOMONTH ( TableName[Date], -1 ) + 1

[Anonymous]

Hi @tamerj1 ,

Thanks for responding.

I've checked "EOMONTH" but It does not work in directquery mode.

[tamerj1]

@Anonymous 

Try

=
DATE ( YEAR ( TableName[Date] ), MONTH ( TableName[Date] ), 1 )

[Anonymous]

No luck with this one as well.

[tamerj1]

@Anonymous 
Have tryed creating it as a measure instead of a column? is this a valid option fro your case?

[Anonymous]

@tamerj1 @amitchandak @Jihwan_Kim @SpartaBI @johnt75